Require Time difference in mm/dd hh:mm

**aaron lewis** · 03-05-2013, 10:36 AM

Hi,

Please find my attached excel sheet, here i Require Time difference in mm/dd hh:mm, how do i go about this.

Am really hoping this forum could help me.

Thanks,
Aaron.

**Kevin UK** · 03-05-2013, 10:41 AM

Hi aaron lewis

For the time difference, do you mean in hours & minutes!
In C2:
=B2-A2

Format the cells as "[h]:mm" and copy down.

**daddylonglegs** · 03-05-2013, 10:51 AM

If mm/dd means months and days try this formula

=TEXT(DATEDIF(A2,B2+(MOD(B2,1)>MOD(A2,1)),"m"),"00\/")&TEXT(DATEDIF(A2,B2+(MOD(B2,1)>MOD(A2,1)),"d"),"00 ")&TEXT(B2-A2,"hh:mm")

**aaron lewis** · 03-06-2013, 02:48 AM

Originally Posted by daddylonglegs

If mm/dd means months and days try this formula

=TEXT(DATEDIF(A2,B2+(MOD(B2,1)>MOD(A2,1)),"m"),"00\/")&TEXT(DATEDIF(A2,B2+(MOD(B2,1)>MOD(A2,1)),"d"),"00 ")&TEXT(B2-A2,"hh:mm")

ok i get the output as
Approval Date1 in mm/dd/yyyy hh:mm Approval Date2 in mm/dd/yyyy hh:mm Require Time difference in mm/dd hh:mm
11/16/2012 09:41 11/20/2012 05:36 00/04 19:54 which is proper as 4 days and 19 hrs

Can i get the output only in hours or preferably minutes.

Like for hours : 4days*24 + 19 hrs = 115 hours
or for minutues: 4days*24*60 + 19*60 + 54 = 6954 minutes

**aaron lewis** · 03-06-2013, 03:48 AM

i just realised that the difference should be shwoing 3 days and 19hrs and not 4 days and 19 hrs!!

**Kevin UK** · 03-06-2013, 06:28 AM

What are you trying to do!!!

Look at post #2
For minutes, format as [m]
Or use =(B2-A2)*1440

**daddylonglegs** · 03-06-2013, 10:03 AM

Originally Posted by aaron lewis

i just realised that the difference should be shwoing 3 days and 19hrs and not 4 days and 19 hrs!!

Yes, apologies, to get the correct result in format mm/dd hh:mm this is the correct version

=TEXT(DATEDIF(A2,B2-(MOD(B2,1)<MOD(A2,1)),"m"),"00\/")&TEXT(DATEDIF(A2,B2-(MOD(B2,1)<MOD(A2,1)),"d"),"00 ")&TEXT(B2-A2,"hh:mm")

....but if you want the result in just hours or minutes that can be done more simply as Kevin suggests....