adding minutes (> 60 ) to military time

[grilla79]

I am using a spreadsheet with military time expressed as hhmm (no ":").
I need to be able to add time intervals, expressed in minutes (not i
decimals of hours) to these military times to get estimated timef o
completion in the same time format (HHMM). For example, at 1835 w
started a project estimated to take 145 minutes complete. I can do th
math in my head to see that 145 minutes = 2 hours and 25 minutes for a
ETA of 2050 but I can't make the spreadsheet do it. I'd appreciate an
help

Thanks

Do

 

[budgie]

I am using a spreadsheet with military time expressed as hhmm (no ":").
I need to be able to add time intervals, expressed in minutes (not in
decimals of hours) to these military times to get estimated timef of
completion in the same time format (HHMM). For example, at 1835 we
started a project estimated to take 145 minutes complete. I can do the
math in my head to see that 145 minutes = 2 hours and 25 minutes for an
ETA of 2050 but I can't make the spreadsheet do it. I'd appreciate any
help

Unless you want to put the ":" back in there, I suspect you are going to have to
"pre-process" that interval, pre-process the time into hrs and mins, add and do
the carry processing, and convert the results back to your four digit format.

 

[dcronje]

Assuming start time is in A1 and duration in B1 finish time (ETA) is i
C1 use this formula in C1

=a1+(B1/60/24

 

[budgie]

Assuming start time is in A1 and duration in B1 finish time (ETA) is in
C1 use this formula in C1

=a1+(B1/60/24)

Won't work - the O/P's start time is NOT in an Excel time format, just a number,
so addition won't give modulo 60 carry.

 

[dcronje]

Assume that the cell just contains numbers then on the same basis A1
start time, B1 duration and solution in C1 try this formula in C1 ( it
returns it as a number so ensure all formats are numbers)

=LEFT(TEXT(((LEFT(A1,2)&":"&RIGHT(A1,2))+(B1/60/24)),"hhmm"),2)&RIGHT(TEXT(((LEFT(A1,2)&":"&RIGHT(A1,2))+(B1/60/24)),"hhmm"),2)

I tested this and it worked but it was only quick logic checks though.

 

[Dave Peterson]

One more way:

=TEXT(TIMEVALUE(TEXT(A1,"00\:00")&":00")
+TIMEVALUE(TEXT(A2,"00\:00")&":00"),"hhmm")
(one cell)

This will return a number of 2100 (not 2050) and it still won't be a time.

(I put 1835 in A1 and 145 in A2)

 

[budgie]

Assume that the cell just contains numbers then on the same basis A1
start time, B1 duration and solution in C1 try this formula in C1 ( it
returns it as a number so ensure all formats are numbers)

=LEFT(TEXT(((LEFT(A1,2)&":"&RIGHT(A1,2))+(B1/60/24)),"hhmm"),2)&RIGHT(TEXT(((LEFT(A1,2)&":"&RIGHT(A1,2))+(B1/60/24)),"hhmm"),2)

I tested this and it worked but it was only quick logic checks though.

Yep, that's what i meant by "pre-processing" - to separate hours and mins so
carries can be executed correctly. All this is FAR easier if an Excel time
format is used.