Show Time/Date Diff

Ted Penner
Ted Penner used Ask the Experts™
How would I show the date Wed Sept 11, 2013, 2:30 pm in single columns such that the difference in hours and minutes could be calculated?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

You can just do normal arithmetic on dates, so say subtract one from the other.  Then you format the result cell as "[h]:mm".  This will show the difference in hours and minutes (even for periods longer than a day).

I would suggest you create a database for it

Use Microsoft Access (simple)
or .NEt and MSSQL (harder)

For access, just make table with a few fields ( Employee, inDate, inTime, outDate, outTime)
then make a form to add new items

It should be easy to calculate it that way
Top Expert 2011

And the number format for the date you as for would be "ddd mmm dd, yyyy h:mmam/pm".  The you enter the date and time as usual and it will be displayed as in your original question.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
The actual date and time in your sample workbook was January 0, 1900 9:11 AM. When entering data into the cells, put the date first and then the time. Excel is pretty forgiving when you do so. The following all worked (in Excel 2013 with U.S. short date setting):
9/11/2013 14:30
9/11/13 2:30 PM
9-11-13 14:30
9/11 2:30 PM
9-11 14:30

From the above tests, I conclude:
1. Excel will accept either / or - as a m/d/y separator
2. If you don't enter the year, Excel will assume it is the current year
3. You can use either AM/PM or military time to enter times after noon

I agree with andrewssd3 that you can calculate the number of hours and minutes difference between B2 and A2 as B2-A2.

You can also calculate the difference as MOD(B2-A2,1) provided the answer won't exceed 24 hours. Unlike the simpler B2-A2, the MOD calculation will give the right answer even if a person doesn't enter the dates yet starts at 20:30 and finishes the next morning at 06:30.

To avoid getting invalid answers if both start and finish times haven't been entered, you might use an IF test:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial