General discussion


Elapsed Time in Excel - formula

By mackey77 ·
We rebuild eqipment that may come in on Monday and go out two weeks later on Friday. I am looking for an Excel solution that will give me total days, hours, and minutes that the project took to complete. A sample spreadsheet would be helpful.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Cambria777 In reply to Elapsed Time in Excel - f ...

It's mainly a formatting problem. Example:

Column A = Date/time received
Column B = Date/time shipped out
Column C = Number of Days, Hours, & Minutes from time received to time shipped out.

First, format the cells in columns A & B with a date/time format such as: 3/14/98 1:30 PM. (If you don't know how, select columns A & B, then right click & choose FORMAT CELLS, then select the NUMBER tab, choose DATE category, then find the format you want.)

Second, format the cells in column C with a CUSTOM number format (same steps as above, except choose CUSTOM category & enter your own format), such as: dd, hh:mm (This stands for 2 spaces for DAYS, 2 spaces for HOURS, 2 spaces for MINUTES, with a comma after the number of days and a colon separating the hours and minutes.)

Enter dates & times into both columns A & B. If you put the date & time in column A that an item was received and put the date & time in column B that the same item was shipped out again, then enter the formula "=B2-A2" in C2 and copy that formula down as far as you need to and you'll see the total days, hours, & minutes for each row.

Example if the above formats are used:
Cell A2: 09/05/2005 8:00:00 AM
Cell B2: 09/17/2005 5:40:00 PM
Cell C2: =B2-A2 which displays as 12, 09:40 and means 12 days, 9 hours & 40 minutes.

Hope this helps.


Collapse -

by mackey77 In reply to

Answer by Cambria 777 was exactly what I needed. Excellent response!


Collapse -

by mackey77 In reply to Elapsed Time in Excel - f ...

This question was closed by the author

Related Discussions

Related Forums