Calculating elapsed days and hours



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

=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:


Was there an easier way??


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

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

Does that help?


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


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!



To ignore times in the date fields use:


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


Place the same formula in say cells E1 and F1

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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question