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?
Book1.xlsx
frugalmuleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewssd3Commented:
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).
0
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
0
andrewssd3Commented:
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.
0
byundtMechanical EngineerCommented:
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:
=IF(COUNT(A2:B2)<2,"",B2-A2)
=IF(COUNT(A2:B2)<2,"",MOD(B2-A2,1))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.