Calculating total time over 24 hours ... [h]:mm does not work

[Endosmok]

I am having an issue summing up total time.

Users are entering start and stop times, in military format, which get summed up each day. The format for these cells are CUSTOM hh:mm

I use a calculation to get the sum of each start/time for total time in a day. Format for these cells are is TIME 13:30 (so if you entered 08:00 to 10:30, TIME output is 2:30)

This works fine as fine I think. Unless the TIME output should be 2.5 hours?

Now I have tried to AUTOSUM feature to get the grand monthly total from the 31 TIME cells as listed above.

It looks like it's calculating properly as I enter the times UNTIL it starts to add over 24 hours. Then it reverts back to 01:00 if for example grand total was 25 hours for the month.

Most of the solutions I've found on the internet state that all I have to do is change the format to [h]:mm

This does not yield the correct answer.

So for this example, let's say the total hours for the month is 25 hours. AUTOSUM shows 01:00 for total time. If I use CUSTOM format with [h]:mm the total time changes from 01:00 to 5929:00, while I am trying to get 25:00 for the correct output.

I am not sure where I went wrong but apparently I did. Any help would be appreciated.

Thanks!

 

[Endosmok]

ok... i no longer think it's the code... i am pretty sure it's my formula for daily total. It only works if the format is TIME 13:30. I took the code out and still shows those funky numbers. So it's gotta be the formula.

The formula is a little cumbersome. But it was the only one i could figure out that let me calculate time differences when ending time is lower than starting time. For example 2200 to 0200. That formula would give me 4 hours, then go to the next column and do the same if time existed. Then sum up those values to the daily total.

I think the formula is somehow introducing values to get that sum?

ideally it would be multiple start/stop times in a day. differences subtracted then totaled for the day either in decimal or time.

=SUM(IF(B13>B12,B13-B12,B13+1-B12),IF(C13>C12,C13-C12,C13+1-C12),IF(D13>D12,D13-D12,D13+1-D12) this is the formula in column E. This works but not when i total for all days.​

	B	C	D	E
12	1400	1900	2200	daily total hours
13	1800	2100	0200	10.00 or 10:00

<tbody>
</tbody>

I believe i may have to start from scratch... though i was so close to what i needed it to do.

 

[Jonmo1]

So that formula extends out to column K right?

Are the values in those cells a mixture of Text and Number, if some of them are text, and some number, that will explain the problem.
They need to be uniform. ALL numeric time values.

Don't look at cell formatting, that's not what matters.
Test with ISNUMBER
=ISNUMBER(B13)

Do that for every cell involved. Are they ALL TRUE, or FALSE, or a mixture?

 

[Endosmok]

So that formula extends out to column K right?

Are the values in those cells a mixture of Text and Number, if some of them are text, and some number, that will explain the problem.
They need to be uniform. ALL numeric time values.

Don't look at cell formatting, that's not what matters.
Test with ISNUMBER
=ISNUMBER(B13)

Do that for every cell involved. Are they ALL TRUE, or FALSE, or a mixture?

Formula and table are edited above. Hopefully that gives you better picture. the columns of B,C, and D are formatted as CUSTOM [hh]:mm and also have that VB code applied to them. Column E where it uses the formula is formatted as TIME 13:30 with no VB code. This yields the correct SUM but at the cost of doing something to the value where I cannot total the entire month properly using AUTOSUM or any other method.

Now when columns b,c, and d are blank on other rows (days), the daily total in E shows a value of 0:00. If I change Column E to [hh]:mm that is where I get those large numbers (under 240:00) when times have values, and those rows that have no times would now show 240:00 in the SUM even though there are no time values.

 

[Endosmok]

Are the values in those cells a mixture of Text and Number, if some of them are text, and some number, that will explain the problem.
They need to be uniform. ALL numeric time values.

So those values on the start/stop times are formatted CUSTOM hh:mm without any numeric start/stop times. When I enter 1600 into a value it converts to 16:00 and the FORMAT is changed to CUSTOM [hh]:mm instead of hh:mm. maybe the VB code does that or excel. But all start and stop time cells are the same format as listed. And i have only input numbers 0 thru 9 for time. Unless the : in the VB code is making it a mixture?

Testing with ISNUMBER gives me true where values exists in start and stop times and FALSE where they don't. All daily totals are TRUE as they have either a total 5:00 or 0:00. Grand total comes back with TRUE as well.

 

[Endosmok]

Here's a link to the excel sheet that i am having issues with if it helps anyone help me

https://1drv.ms/f/s!At-tl7w-xzikiMVpI3_dsUrKqygtXA

 

[Jonmo1]

Ok, here's the problem, your daily formula is like this
=SUM(IF(B9>B8,B9-B8,B9+1-B8),IF(C9>C8,C9-C8,C9+1-C8),etc..

There are alot of blanks expecting future entries. So in the book you posted, you have Data in B8 and B9, but not C8 and C9
If B9 and/or B8 is blank, then B9>B8 is FALSE. So that first IF then returns B9+1-B8, which would end up 1-0, = 1.
So all those blanks are being summed as 1, not 0.
1 = 1 Day = 24 Hours, the source of the large results.

Try this instead of that long sum(if, if, if) formula.
This would go in M8
=SUMPRODUCT(MOD(B9:K9-B8:K8,1))

 

[Endosmok]

Ok, here's the problem, your daily formula is like this
=SUM(IF(B9>B8,B9-B8,B9+1-B8),IF(C9>C8,C9-C8,C9+1-C8),etc..

There are alot of blanks expecting future entries. So in the book you posted, you have Data in B8 and B9, but not C8 and C9
If B9 and/or B8 is blank, then B9>B8 is FALSE. So that first IF then returns B9+1-B8, which would end up 1-0, = 1.
So all those blanks are being summed as 1, not 0.
1 = 1 Day = 24 Hours, the source of the large results.

Try this instead of that long sum(if, if, if) formula.
This would go in M8
=SUMPRODUCT(MOD(B9:K9-B8:K8,1))

wow... that did the trick! tell me where to send the xmas gift...

one other little issue... I have a merge cell warning the comes up on the 2nd line of time... so the end time (I believe this only happens in column B, which is the first time entry column.) So if i enter a valid time it says "can't to that to a merged cell" and explains what is going on. But i am not sure why it's occurring? Is it because I merged the first column? or somewhere else? It still works after I click ok... but that warning will get annoying quickly.

Again thanks... saved me hours of head scratching on that new formula.

 

[mole999]

unmerge the cell and in the same formatting box change it to centre across selection

 

[Endosmok]

unmerge the cell and in the same formatting box change it to centre across selection

To me it doesn't look merged to begin with. I never set it up as a merged cell. I selected a cell (that gives me the merge message) selected UNMERGE then centered but it had no effect. Same error keeps popping up.

 

[mole999]

from your posted book
Rows 1,2,3,4,5

Col A, L, M all have merges