MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formulas using time

Posted by Deirdre Heffernan on May 18, 2001 7:08 AM

I am having a problem setting up formulas using time. Here is the data I collect in spread sheet.
Col A - Travel (to) time in minutes (example :15)
Col B - Arrival time (example 11:30)
Col C - Begin time (example 12:09)
Col D - Finish time (exampe 13:20)
Col E - Travel (from) time in minutes (example 1:01)

Here are the formulas I need:
Col F - Total travel time (A+E) - can't get this to work.
Col G - Work time (D-B) this works
Col H - Total time (G + F) - can't get this to work.

Any suggestions?

Posted by Mark W. on May 18, 2001 7:17 AM

Use Excel's elapsed time formats (e.g., [h]:mm)

Posted by Dave Hawley on May 18, 2001 7:19 AM

Hi Deirdre

So long as your times in Columns A:E are real times (see Tips on entering dates and times in Excel help) you can use the formulas as you have. But you will need to select Column F:H and go to Format>Cells>Numbers-Custom and select [h]:mm:ss. this will show hours greater than 24.

DaveOzGrid Business Applications