# Converting a travel time to an Excel time

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?

-
-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. – r4. Jan 24 '12 at 7:32

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.

-
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 '12 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 '12 at 17:45

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

-