Calculating duration and totaling hours over two dates

[jadnich]

I have a spreadsheet which tracks the durations of events in hours and minutes, and then totals these durations over the entire month. Some of these events start on one day, and end after midnight on another. The problem I am encountering is that events which span two different dates end up calculating as hundreds of hours in duration. I would appreciate any advice to fix my error.

The source data does not associate date and time together, so the actual cell value for the times all show 1/1/1900. I format the cell to show just hours and minutes, in [time] 13:30 format, but the 1/1/1900 date is still part of the data. When trying to calculate the duration of an event starting at 11pm and ending at 12:30 am, my cells display 23:00 and 0:30 respectively. I can make the duration column display 1:30 as the total, but when I sum up all of the monthly durations, these specific totals do not calculate as an hour and a half, and rather as hundreds of hours (554:00:00, to be exact). This is because it is trying to calculate the difference between 1/1/1900 at 11pm and 1/1/1900 at 12:30am. Strange that it doesn't just give me an error here.


The table below is how my data appears, formatted as [time] 13:30. The Start and End times are cell references to another sheet where I try to remove the date, and the Duration is a calculated field. The total shown in duration is the desired result for this stage.

E	F	G
Event Start	Event End	Duration
23:00	0:30	1:30

<tbody>
</tbody>

The table below shows the formula I used to both try to eliminate the 1/1/1900 from the original source data, and to calculate the duration over the 24 hour mark. The actual function in Event Start and Event end is a cell reference to a different sheet where I perform the shown function. I have listed the function from the other sheet below, and bypassed the cell reference step.

E	F	G
Event Start	Event End	Duration
=TIME(HOUR('Source Data'!E2),MINUTE('Source Data'!E2),SECOND('Source Data'!E2))	=TIME(HOUR('Source Data'!F2),MINUTE('Source Data'!F2),SECOND('Source Data'!F2))	=IF(F3-E3>0,F3-E3,IF(F3-E3=0,0,24-(E3-F3)))

<tbody>
</tbody>

So, by asking Excel to look at the source data (containing the date 1/1/1900), and just return the time from that value, I had hoped to eliminate the problem. Then, I have the calculation function that will subtract the start time from the end time if that total is greater than 0. If that formula results in a negative number (which is the case when it spans two dates), then subtract in reverse and subtract the whole thing from 24 hours. If there is no source data in the template, the Event Start and End time display as 0:00, so I added in the second IF function to result in 0:00 across the board just to keep the template looking clean.

The Duration field itself (when formatted as [time]13:30) displays the correct result. As soon as I convert it to [h]:mm:ss, it shifts to 554:00:00. If I total up all of the durations for a month in any format, the resulting total has to be in [h]:mm:ss format and my total is way off.

I hope I have covered all aspects of my issue clearly. If anyone has a solution, I would be glad to hear it. Keeping in mind:

• The source data will ALWAYS start out having 1/1/1900, because of the format of the initial export.
• The actual event date exists, but as a separate field. If need be, I could add a step where the date and time are combined, but I wouldn't prefer it if a better solution is available
• All events which do not span the midnight mark are calculating perfectly fine all the way through
• The final result needs to be in hours and minutes, hours as an integer (with minutes as a decimal), or as total minutes.

thanks for the review and assistance!

 

[thisoldman]

I'm assuming all duration times are less than 24 hours. Start is in E2, end is in F2.

Try
=(F2 < E2)+F2-E2<e2)+f2-e2< html=""></e2)+f2-e2<>

 

[jadnich]

That worked. Thanks! It also works without the need for the TIME formula, which simplifies things greatly.

Could you explain the context of how the formula works? (F2 < E2) results in TRUE for the problematic items and FALSE for the standard ones. I think the part I am missing is how Excel is responding to the TRUE statement.

 

[thisoldman]

Thanks. I'm glad to have helped.

Excel will happily convert TRUE to the value 1 (one) and convert FALSE to 0 (zero) when you use them in a mathematical operation: addition, subtraction, multiplication, or division. So the formula is equivalent to:
=IF(F2 < E2, 1, 0) + F2-E2

Excel will also convert zero to FALSE and any other number to TRUE when used in a Boolean operation. So you could construct a conditional formula like this:
=IF(A1, "A1 is a number not equal to zero or TRUE", "A1 is a zero, an empty string, a blank cell, or FALSE")

In large spreadsheets, IF formulas may cause slowdowns in calculations. Eliminating IFs then provides a benefit.