up vote 0 down vote favorite
share [g+] share [fb]

I have calculated the time it takes to travel from point A to point B. The results are in number of seconds. I have converted the number of seconds to Hours, Minutes, and Seconds in three cells. I want to combine the three cells and use the result in a time calculation to calculate arrival time based on a given start time.

CONCATENATE produces a result that appears correct but can not be used in a time calculation. 

=CONCATENATE((RIGHT(CONCATENATE("00",I46),3)),":",(RIGHT(CONCATENATE("0",J46),2)),":",(RIGHT(CONCATENATE("0",K46),2)))

TIMEVALUE also produces a result but it does not allow for values > 24 hours. 

=TIMEVALUE(I46&":"&J46&":"&K46)

Do you have a solution that will allow me to calculate the arrival time?

link|improve this question
1  
-1 because it seems that you do not follow f.a.q. 'do your research'. I have not used excel formels for a while but I suspect the problem is far from hard. – Olof Edler Jan 24 at 7:32
feedback

2 Answers

up vote 0 down vote

The formula below is one method of adding the following cells:

H25    A date
H27    A number of hours which could be more than 24
I27    A number of minutes
J27    A number of seconds

=DATE(YEAR(H25),MONTH(H25),DAY(H25)+INT(H27/24))+TIME(HOUR(H25)+MOD(H27,24),MINUTE(H25)+I27,SECOND(H25)+J27)

I have used the following functions: DATE, TIME, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MOD and INT. Look those up and you will understand what this formula does and be able to tidy it up to meet your exact requirements.

link|improve this answer
Hello Tony, as you are using HOUR(H25), MINUTE(H25) and SECOND(H25) I'm assuming that H25 is supposed to contain a date and time. I don't believe that formula will give the correct results when HOUR(H25)+MOD(H27,4) is 24 or greater. I believe you can simplify greatly - see my answer – barry houdini Jan 24 at 16:04
@barry houdini. The OP refers to a Start time so yes H25 is a date/time which I should have stated more clearly. I do not believe I simplify greatly but I agree that I have not thought of the two hour figures summing to more than 24. Good point. I have not retested but I believe INT(H27/24) should be INT((HOUR(H25)+H27)/24) and HOUR(H25+MOD(H27,24) should be MOD(HOUR(H25)+27),24). I suspect that you are correct and splitting the duration into its components is a mistake but with so little information I was not confident enough to mention it. – Tony Dallimore Jan 24 at 17:45
feedback
up vote 0 down vote

Are you only converting the seconds into hours, mins and seconds to help you in the calculation? If so you shouldn't because it just makes things more complex

If you have travel time in seconds in A1 and start time/date in B1 then this formula in C1 will give arrival time/date

=A1/86400+B1

...if you do want to split the seconds into hours mins and seconds in I46, J46 and K46 then this formula will give arrival

=H46+((K46/60+J46)/60+I46)/24

assuming start time/date in H46

format result cell as date/time

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.