EXCEl Time Difference

Hi
I have a excel spread sheet in which I want to calculate time difference between two time
Time 1:  1:45 PM
Time 2 : 1:55 PM

Difference = 0:10

Thanks
surah79Asked:
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.

Saurabh Singh TeotiaCommented:
Assuming you have a start time in cell a1 and end time in b1...

In c1 you can simply apply this formula = b1-a1

Then press ctrl+1 and format that cell as time and you will see the necessary results in desired format...

Saurabh...
0
surah79Author Commented:
i did that its not working for all its giving some error #Value!
0
DrewKCommented:
Make sure you have cell format set to "Date"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Wilder1626Commented:
Hi

is this what you are looking for?

Please look at attachment.
=(A2-A1)*1440

Open in new window

Excel-time-difference.xlsx
0
Wilder1626Commented:
The other way to do it is, Assuming you have a start time in cell a1 and end time in A2...:
=TEXT(A2-A1,"h:mm")

Open in new window


If start time in cell A1 and end time in B1, put the below formula in cell C1
=TEXT(B1-A1,"h:mm")

Open in new window

Excel-time-difference.xlsx
0
Saurabh Singh TeotiaCommented:
surah79,

Can you post your file so that i can look into it what's wrong...

Saurabh...
0
frankhelkCommented:
A bit info on the background:

When Excel stores dates (and times) it does so by using a floating point number of "days since 01.01.1900 00:00:00". Time values are stored as fraction of a day, where 1.00 represents 24 hours.

That way you could simply get the difference of two datetime values by just subtracting one from another.

If you want the number of [seconds, minutes, hours, days, etc.] between two datetime values, you'll have to multiply the difference by the number of [seconds, minutes, hours, etc.] in a day.

Days  = (A1-B1)
Hours = (A1-B1) * 24
Minutes = (A1-B1) * 24 * 60
Seconds = (A1-B1) * 24 * 60 * 60
Milliseconds = (A1-B1) * 24 * 60 * 60 * 1000

And the other direction:

Weeks =  (A1-B1) / 7
Months (approx. ... that's a bit fuzzy) = (A1-B1) / 30
Years (approx.) = (A1-B1) / 365.25

With some effort you could split the difference into separate parts, i.e.

Minutes = INTEGER((A1-B1)*24*60)
RemainingSeconds = (((A1-B1)*24*60)-INTEGER((A1-B1)*24*60)) * 60
0
Wilder1626Commented:
If you just use this: =TEXT(B1-A1,"h:mm"), you will get exactly what I think you are looking for.
Time difference
0
Rob HensonFinance AnalystCommented:
I suspect the reason why Surah79 is having issues is that the time values in the cells are actually text so will need converting to number before doing any calcs on them.

=(B1*1)-(A1*1)

If you want the end result as text as well then you can adapt Wilder1626's suggestion:

=TEXT((B1*1)-(A1*1),"hh:mm AM/PM")

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
Hmm, actually even if the entries are text rather than number, just putting them through a simple B1-A1 formula "should" force Excel to recognise the entries as Time and do the necessary calculation.

Therefore, I can only assume that the entries are text but not in a recognisable format.

Thanks
Rob H
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
surah79Author Commented:
Thanks a lot everyone.
0
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.

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.