Calculation of Time/Date

Good morning,
I am having a little trouble trying to calculate the difference between two dates/times field. The fields come in as a Date field and time field (there are 2 sets) one for Date of call, Time of Call and Date of Death, Time of Death. I am looking to find the difference betweent the two. If the difference is 1hr or over the cell needs to turn red. I can get the cell formatting to turn red with the conditional formatting but something isn't right. I have attached a spreadsheet that illustrates what I am attempting.


The logic: If TOC >= TOD AND TOD >=1hr, Cell turns red

Can someone take look and point me in the right direction?

Larry

Hello Larry,

Typically to combine a date and time you should add them not concatenate, so F3 would be

=A3+B3

and G3 would be

=C3+D3

format these cells as

dd/mm/yy hh:mm;;

Now you could use a formula like this in E3

=IF(AND(F3>0,G3>0,F3>G3),F3-G3,"Check IDR or comments")

should the time/date of call always be later than the time/date of death?

Note: you could do without columns F and G entirely if you wished and use a formula like this in E3

=IF(AND(C3+D3>0,A3+B3>0,A3+B3>C3+D3),A3+B3-C3-D3,"Check IDR or comments")

to answer your question: sometimes they "jump the gun" and call early if the death is "imminent".

Thank you for the lesson. I don't know why I did the CONCATENATE formula, but that also answers why I would see 2084 for the year when I coverted that number to the date. It kinda threw me off.

THANK YOU SO VERY MUCH!!!

Larry