Hi all

I am tying my brain in knots trying to work out how I get the time difference in seconds between a long time on one day and a long time on the next. This is because I am at a hospital where transplants are done, and we want to know the exact time down to the second between time of death and the new organ going in. The time lapse should be c.12hrs maximum but of course that can be any combination ie death late pm transplant early am next day, death early am, transplant later same day. Any help most welcome :confused: :eek: :(

Use the DateDiff() function between the two date/time fields and use the "s" identifier with it.

Ok, firstly I made a mistake, I meant minutes not seconds, so I replaced "s" with "n" , but it still doesn't work: I put this in the field line:

CI time: DateDiff("n",[New liver in],[Time Circ arr])

CI = cold ischaemia time (the time diff that I want), Time Circ arr= time of death

one example : Time Circ arr was 00:35:00 (ie 35 mins past midnight), New liver in 12:50:00 later that day. The cold ischaemia time should be 769 and it's giving me -735

another example : Time Circ arr 21:24:00, NLI 11:19:00 (ie on the next day) - CIT should be 835 minutes, my calculation gives 605. Any ideas please?

Why are you not putting the date in with the time - that's your problem. It's a date time field so it should hold the date and the time.

OK thanks - but which date, date organ was taken or date put in (which sometimes is next day but not always) and whereabouts in the string do I put the name of the record where the date is?

Replace StartDate with the first date.
Replace EndDate with the second date.

=DateDiff("n", StartDate, EndDate)

Thanks, I checked our database and we don't record the date of donation of the new organ, only the date of transplant, so perhaps we can't do this calculation...?

No. Not without both dates. If the donation date is never earlier than yesterday, you can "guess" whether the donation was today or yesterday but the "guess" will sometimes be inaccurate. You can compare the two time fields and if the donation time is > the transplant time, you can assume the donation date was yesterday. Otherwise, the donation date was earlier today or early yesterday. There is no way to tell.

Thanks very much for your replies everyone... I'm going to wait until I go on a VBA training course to work out an "if time =>X" then... etc type string (donation is never further away than yesterday), which would be nice as it would avoid me having to go back through over 100 donors and inputting the donation date (I'm slowly working through inputting all 800+ of them)!!

Can't you modify the database so that dates can be entered with times?

Hi

Yes I can and probably will have to... I was hoping to avoid it as it'll mean adding a field to the dbase, then backtracking through 100 odd donors that I inputted already and putting their donation dates in!

I was hoping to avoid it as it'll mean adding a field to the dbase

Not if the field holding times is already in Date format. It will hold date AND time in a single field.


backtracking through 100 odd donors that I inputted already and putting their donation dates in!

Unfortunately, yes.