# Calculating elapsed days and hours

G

#### Guest

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

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??

G

#### 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?

G

#### Guest

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

G

#### 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

G

#### 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