adding minutes (> 60 ) to military time

G

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
 
B

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.
 
D

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
 
B

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.
 
D

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.
 
D

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)
 
B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top