calculating time over 24 hours

[CRusso1216]

Good afternoon! I have to calculate the length of stay (LOS) of patients in the hospital. Some can be here for over 24 hours. When I use the formula =EndTime-StartTime + (EndTime<StartTime) it returns a time less than 24 hours, so a LOS of 24 hours and 22 minutes looks like 0:22:00 rather than 24:22:00. I have formatted the cell [h]:mm:ss. I feel like I have searched every message board on the internet and have tried different formulas and formatting, but it still will not return a time over 24 hours. Any help would be appreciated. I'm currently calculating hundreds of values by hand!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start Date[/TD]
[TD]Start Time[/TD]
[TD]End Date[/TD]
[TD]End Time[/TD]
[TD]LOS[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6/12/17[/TD]
[TD]21:04:00[/TD]
[TD]6/13/17[/TD]
[TD]21:26:04[/TD]
[TD]0:22:04[/TD]
[/TR]
</tbody>[/TABLE]

 

[CRusso1216]

It seems the rest of my post may have been cut off. To continue before the table... When I use the formula =EndTime-StartTime + (EndTime<StartTime), it returns the time less than 24 hours, so 24:22:04 returns as 0:22:04. I have the cell formatted as [h]:mm:ss. I feel like I have searched the internet for ideas and everything I try keeps returning the same number. Any help would be appreciated as I am currently calculating hundred of values by hand!

 

[Joe4]

Are you using Greater Than or Less Than signs?
See: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

 

[Roxxien]

Hello CRusso1216, even if I don't see everything in your post, I think I understand.
Your problem seem to be the format of your "L2" cell. I suggest you to create a custom format like this : d hh:mm:ss so that you can follow the number of day if more than 24 hours.

 

[Eric W]

Right, use this formula:

=K2+J2-(H2+I2)

and the custom format that Roxxien suggested.

Note: Excel can store a date/time in one cell, so this assumes that H2 and J2 are dates only.

 

[roscoe]

The easiest way to do time is to remember that the day is the number left of the decimal (the integer) and the part after the decimal is the fraction of the day (i.e. xxxxx.5 is noon on day xxxxx). I always try to keep them together whenever possible to make the math easier. You can always use time or date formats to display only one or the other as you wish.

 

[CRusso1216]

Eric W, Thank you sooo much!!! Your formula worked. I've literally been trying to figure this out for 1.5 years.

 

[Eric W]

Glad we could help! It sometimes takes a while to wrap your head around how Excel saves dates/times and how to use them.