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!

 

[mole999]

small set of examples, [h]:MM does work, so there must be something else going on 1 = 24 hours, something in the formulas

 

[Jonmo1]

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

Perhaps the times being summed are actually Date+Time..
Can you change the format of THOSE cells to [hh]:mm (not just the cell with your sum formula in i)

 

[Endosmok]

small set of examples, [h]:MM does work, so there must be something else going on 1 = 24 hours, something in the formulas

Indeed something that i am doing is messing with the numbers. So I do have some code in there that I found that let's the user enter in 0800 which converts it to 08:00 military time. This code applies to the start/stop time cells.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5:K54")) Is Nothing Then Exit Sub
Dim xHour As String
Dim xMinute As String
Dim xWord As String
Application.EnableEvents = False
xWord = Format(Target.Value, "0000")
xHour = Left(xWord, 2)
xMinute = Right(xWord, 2)
On Error Resume Next
Target.Value = TimeValue(xHour & ":" & xMinute)
On Error Resume Next
Application.EnableEvents = True
End Sub

In addition, for daily sum I have to account for times for when a start time begins in one day and ends in another. For example, start time of 2200 end time of 0200. I do get the correct answer of 4:00 total hours but i use this formula to do so. It's when I try to add up these daily totals (4:00 one day, 7:00 the next where it hits 24 hours and goes back to 01:00 at 25 hours).

=SUM(IF(B9>B8,B9-B8,B9+1-B8),IF(C9>C8,C9-C8,C9+1-C8) and this continues for different day rows.


One of those two things are messing with my total time for the month... but can't quite figure it out.

 

[Endosmok]

Perhaps the times being summed are actually Date+Time..
Can you change the format of THOSE cells to [hh]:mm (not just the cell with your sum formula in i)

So I changed daily total TIME 13:30 format to [h]:mm and I get those high numbers. Those numbers (using the 25 hours example total) give me the that high value of 5929 above. Also, where days have not occurred in my daily total format of TIME 13:30, it shows 0.00 and not blank. When i changed those cells to [hh]:mm or [h]:mm it changes all those 0.00s to 240:00. 2:00 is 218:00, 4:00 is 220:00.

So if i changed the Monthly total formats (based off daily [hh]:mm)

Using TIME 13:30 i get 5:00 for 25 hours.
Using CUSTOM [hh]:mm i get 5929 for 25 hours.

Monthly results based off daily TIME 13:30 format
Using Time 13:30 it works until I get passed 24 hours
Using CUSTOM [hh]:mm results in 5929 for 25 hours

 

[Jonmo1]

So I take it those cells are doing basic =B2-A2, and A2 and / or B2 contains a DATE+Time, that explains the high numbers.

 

[Endosmok]

So I take it those cells are doing basic =B2-A2, and A2 and / or B2 contains a DATE+Time, that explains the high numbers.

Maybe ...

That code that converts 0800 to 08:00 only affects the cells with start/stop times. I don't have it applying to daily total time. I THINK the problem lies in my daily sum.

I was thinking that maybe I could just convert that daily SUM from TIME to a Decimal and getting monthly total hours using decimal but to no avail. M8 represents my first row of a daily total. So right now M8 shows TIME 13:30 for cell format and holds the value of 4:00 but get 220 in my conversion when it should be 4.0.

=CONVERT(M8,"day","hr") results in 220 after conversion.
M8*24 results in 0.00

​

 

[Jonmo1]

This feels like it's turning into a wild goose chase. We're talking about apples, you're talking about oranges.

Can you do this, remove all cell formatting from all the involved cells (set them all to General). The Start and Stop Cells, and your daily sum cell, and the cell that SUMMS the daily sum cells.
What EXACTLY do you now see in those cells ?
And what is the formula in your daily sum cell ?

 

[Endosmok]

Removing all formatting. And leaving a few sample times there. Those military times are showing as decimal numbers... like 0.666667, 0.8333333 etc. total number daily for cells with values are 9.1666, 9.4166667, etc. Those same daily cells with no value whatsoever show 10 as a value.

This is with leaving that VB sheet code in of course. This may be the culprit. I can easily remove it, but would like another solution for converting 1400 to 14:00 (2pm) or 1630 to 16:30. Just makes data entry much faster w/o having to enter the :

And i do apologize for this mess... i probably did it the complete opposite way that i should have.

 

[Jonmo1]

I think some or all of your start / end times may actually be text values.
Even though your macro is entering a real time, if the cell's format is TEXT, then it will be entered as text.

Try adding this before the Target.Value = TimeValue line
Target.NumberFormat = "[hh]:mm"