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]

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

Col A, L, M all have merges

True, that is correct. So those merged rows and columns are causing the merge errors in other columns and rows? That's what is confusing me.

 

[Jonmo1]

It's not clear exactly what action you take that prompts an error.

Which cell exactly do you enter a value in (what value), and what exactly is the error message recieved?
I notice it's a macro enabled book (the code to convert 0800 to real time 08:00)
Does the problem still occur if you disable that macro?

 

[mole999]

no guarantee they are the full cause, mergiing causes some fun addressing issues, though i can't see any

 

[Endosmok]

It's not clear exactly what action you take that prompts an error.

Which cell exactly do you enter a value in (what value), and what exactly is the error message recieved?
I notice it's a macro enabled book (the code to convert 0800 to real time 08:00)
Does the problem still occur if you disable that macro?

So the 2nd row in the times. So starting at B9, B11, B13 and so forth. I believe it happens in other columns but you can replicate easiest in column B. I enter a time like 0800, hit enter the message pops up that says "We can't do that to a merged cell". If I click OK in the warning box it continues and works just fine. It explains what is occurring but the cell isn't merged that this is occurring in, or at least I don't think it is.

I'll disable the marco and see what happens as well.

 

[Jonmo1]

I can't duplicate the problem, with the book you shared.
But just an opinion here that Merged Cells actually 'Cause' more problems than they solve.
So just remove them and be done with it.

 

[lhartono]

1. Just a suggestion for you to change your worksheet like this , can hide the trip hours (row 10,13,16).

2. Better store Date+Time value.

3. Break long formula into smaller chunks.