# 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
###### Who is Participating?

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

Commented:
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...
Author Commented:
i did that its not working for all its giving some error #Value!
Commented:
Make sure you have cell format set to "Date"
Commented:
Hi

is this what you are looking for?

``````=(A2-A1)*1440
``````
Excel-time-difference.xlsx
Commented:
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")
``````

If start time in cell A1 and end time in B1, put the below formula in cell C1
``````=TEXT(B1-A1,"h:mm")
``````
Excel-time-difference.xlsx
Commented:
surah79,

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

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

Experts Exchange Solution brought to you by