Converting to Days, Hours and Minutes

**StaceyB** · 02-29-2012, 10:16 AM

Hi all

I am using the following formula to get a time from 1 start date to another (without weekends)

This is giving me a value in days, but I need this broken down to Days, Hours and Minutes... any help appreciated.

This is what i am using where : Start date (and Time is ) C3 and end date(and time) is C4.
=((C4-C3))-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(C3)&":"&INT(C4))),2)>5))

Thanks

Stacey

**StaceyB** · 02-29-2012, 10:20 AM

Should have mentioned I am using the start and end times in the following format : (13/01/2012 08:43:00)

**daddylonglegs** · 02-29-2012, 10:31 AM

Hello Stacey,

If dates are always within Monday to Friday period then try this formula

=IF(COUNT(C3,C4)=2,NETWORKDAYS(C3,C4)-1-(MOD(C4,1)<MOD(C3,1))&" days "&TEXT(C4-C3,"hh:mm"),"")

**StaceyB** · 02-29-2012, 10:39 AM

Thanks daddyLonglegs... works a treat. Just amended to add in seconds and its all good!

Thanks again

( =IF(COUNT(C3,C4)=2,NETWORKDAYS(C3,C4)-1-(MOD(C4,1)<MOD(C3,1))&" days "&TEXT(C4-C3,"hh:mm:ss"),"")

**StaceyB** · 02-29-2012, 10:48 AM

Can I just check what the folmula would be if I wasnt excluding weekends ?

**ChemistB** · 02-29-2012, 11:03 AM

It would simplify to

=IF(COUNT(C3,C4)=2,INT(C4-C3) &" days "&TEXT(C4-C3,"hh:mm:ss"),"")

**StaceyB** · 02-29-2012, 11:18 AM

Actually wondering now if my original =((C4-C3)*24)-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(C3)&":"&INT(C4))),2)>5))*24
is a bit overqualified... anyone have a simpler version ?

Where : Start date (and Time is ) C3 and end date(and time) is C4.

**daddylonglegs** · 02-29-2012, 02:42 PM

Yes, to get decimal weekday hours between the two times you can use this formula

=(NETWORKDAYS(C3,C4)-1+MOD(C4,1)-MOD(C3,1))*24

As with your formula that only works OK if C3 and C4 are always on weekdays