Show Time/Date Diff

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?
Who is Participating?
byundtConnect With a Mentor Commented:
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:
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).
Ess KayEntrapenuerCommented:
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
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.