Calculating elapsed days and hours

[Guest]

I figured out a formula for this but now I wonder if there would have been an
easier way.

Here's the situation. I had four separate cells - Admit Date (A1), Admit
Time (B1), Discharge Date (C1), Discharge time (D1).

I needed to calculate the number of days and the number of hours/minutes
spent in the hospital.

(If a person checked in on 8/1 at 3:00 pm and checked out on 8/2 at 2:00 pm
- it was NOT 1 day - it was 0 days, 23 hours.)

So here is my forumula. To calculate the number of days spent in the
hospital:

=IF(C2-A2>0,IF(D2>B2,DATEDIF(A2, C2, "D"),IF(D2=B2,C2-A2,C2-A2-1)),0)

To calculate the number of hours/minutes spent:

=IF(D2>0,IF(B2>0,IF(D2>B2,D2-B2,(D2-0)+(12-B2)),0),0)

Was there an easier way??

 

[Guest]

=(C1-A1) +(D1-B1) and format cell as dd:hh:mm to give time as
days:hours:minutes

In your example, reslt would be 00:23:00

Does that help?

 

[Guest]

or put the SAME formula in two cells, and format one as "dd" and the other as
"hh:mm"

 

[Guest]

Two problems with that. They wanted the days in a separate column from the
hours/minutes. And the data I had to work with, some of the date fields also
had times entered in them - some didn't. So I couldn't just subtract the
values in those two fields - the result wasn't reliable.

But, for future reference, it's a good way to do it. Thanks!

Lauri

 

[Guest]

To ignore times in the date fields use:

=(INT($C1)-INT($A1))+($D1-$B1)

Or if time fields are zero, i.e. times are in date fields:

=IF(AND($B2=0,$D2=0),$C2-$A2,(INT($C2)-INT($A2))+($D2-$B2))

Place the same formula in say cells E1 and F1

Format E1 as custom==>dd and F1 as Custom==>hh:mm

HTH