Excel formula to calcumate difference between 2 date time

[Jagdev Singh]

Hi Experts

I am not able to pull the time difference between 2 date times

Example –

8/26/2015 22:05:00 PM

8/26/2015 22:30:00 PM


Ans – 0:25:00

I tried the formula –

=(B1-A1)*24

I am not able to get the result. Is there any alternative to get the desire output.

Regards,

JD

 

[Chihiro]

Simply =B1-A1

Then format the output cell as [h]:mm:ss

 

[Chihiro]

Oops, didn't realize you had 24 HR format combined with AM/PM. That's bad as Excel won't recognize it as valid time format.

In that instance you want to use formula below to strip out AM/PM portion.
=LEFT(B1,LEN(B1)-3)-LEFT(A1,LEN(A1)-3)

Format output cell as [h]:mm:ss

 

[Jagdev Singh]

Hi Chihiro

Thanks for your formula, but it is giving some fraction value 0.041 instead of 1:00:00 as my desire output.

Regards,
JD

 

[Chihiro]

You need this part.

Format output cell as [h]:mm:ss

Right click on cell/range, select "Format Cells".
In Category choose "Custom", in "Type:" type in [h]:mm:ss

If having further issues, please upload sample file, so I can see what exactly is causing issue.

 

[Jagdev Singh]

Hi Chihiro,

The formatting cell did the trick thanks!! a lot.

Regards,
JD