EXCEl Time Difference

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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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...

surah79Author Commented:
i did that its not working for all its giving some error #Value!
Make sure you have cell format set to "Date"
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.


is this what you are looking for?

Please look at attachment.

Open in new window

The other way to do it is, Assuming you have a start time in cell a1 and end time in A2...:

Open in new window

If start time in cell A1 and end time in B1, put the below formula in cell C1

Open in new window

Saurabh Singh TeotiaCommented:

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

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
If you just use this: =TEXT(B1-A1,"h:mm"), you will get exactly what I think you are looking for.
Time difference
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.


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")

Rob H
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.

Rob H

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.
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.