formula to calculate difference between 2 days excluding weekend or other holidays

[Rem0ram]

Hi

I am trying to calculate the difference between 2 dates including time.

I have modified the networkdays formula to get the results but still it shows incorrect date and time.


=NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)&"\"&TEXT(B2-A2,"hh")&"\"&RIGHT(TEXT(B2-A2,"hh:mm"),2)

Example:

Start Date: 22/06/2015 22:19

End Date:22/06/2015 22:49

Result I Got: 1\00\30

But result should be 0\00\30

Regards
Rem0

[Rem0ram]

Hi

Can you anyone advise on the query?

Best
Rem0

[Rem0ram]

HI can any one help on this, still going in circles.

[Rem0ram]

Digged in further and the below formula works fine for dates within a month and its not working as expected if the dates between 2 months.


=NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-1&"\"&TEXT(B2-A2,"hh")&"\"&RIGHT(TEXT(B2-A2,"hh:mm"),2)

Any help/ guidance is much awaited.

Best
Rem0

[Rem0ram]

Digged in further and the below formula works fine for dates within a month and its not working as expected if the dates between 2 months.


=NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-1&"\"&TEXT(B2-A2,"hh")&"\"&RIGHT(TEXT(B2-A2,"hh:mm"),2)

Any help/ guidance is much awaited.

Best
Rem0

[Rem0ram]

Hi

Any advice / guidance?

Still stuck on this...

[mancubus]

check this for calculating working hours:
http://chandoo.org/wp/2010/09/10/working-hours-formula/

you can download the workbook at the bottom of the page...

[Rem0ram]

mancubus...Thanks

I managed to custom the formula for my need, but still facing an issue with that.

If you can take a look on the below link and advise that will be great.

http://chandoo.org/wp/2010/09/10/working-hours-formula/#comment-1005705

[mancubus]

what's that?
i see nothing new there...

[Rem0ram]

may be its still under moderation.

here it is...

I have managed to custom the formula and it works fine but it throws error for below start and end date (all GMT).

Start: 21/06/2015 17:49

End: 22/06/2015 06:55



=TEXT((24*(NETWORKDAYS(A4,B4,RDATA!$AC$2:$AC$71)-1)-24*((MAX(MOD(A4,1),MOD(B4,1))-MIN(MOD(A4,1),MOD(B4,1)))))/24,”dd/hh/mm”)


Not sure what the error is but will be helpful if you can point out anything in the formula.

[mancubus]

i am not sure dd/hh/mm is a valid date/time format.
second issue is, especially when you copy a formula from a web site, make sure special characters (such as single or double quotes) are valid excel characters...

[Rem0ram]

Apologies!

here you go...


=TEXT((24*(NETWORKDAYS(A5,B5,RDATA!$AC$2:$AC$71)-1)-24*((MAX(MOD(A5,1),MOD(B5,1))-MIN(MOD(A5,1),MOD(B5,1)))))/24,"dd/hh/mm")

for the dd/hh/mm have tested and works fine...

if you can advise why the above formula fails for the date range given above will be great help...

[mancubus]

see if this helps...
http://answers.microsoft.com/en-us/office/forum/office_2003-excel/how-format-cells-to-show-dd-hhmm-greater-than-30/3716005c-290f-420a-a3bc-889eedeba755?auth=1

as you can see i am just googling... :)

[Rem0ram]

mancubus...

managed to break thru...

May be it might help others....



=IF(AND(DAY(A2)<>DAY(B2),MONTH(A2)<>MONTH(B2),OR(MOD(A2,1)>MOD(B2,1),MOD(A2,1)<MOD(B2,1))),TEXT((24*(NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71))-24*((MOD(A2,1)-MOD(B2,1))))/24-1,"d/hh/mm"),IF(AND(DAY(A2)<>DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)<MOD(B2,1)),TEXT((24*(NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71))-24*((MOD(A2,1)-MOD(B2,1))))/24-1,"d/hh/mm"),IF(AND(DAY(A2)<>DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)>=MOD(B2,1),MAX(A2:B2)-MIN(A2:B2)>=6),NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)-1&"/"&TEXT(B2-A2,"hh")&"/"&RIGHT(TEXT(B2-A2,"hh:mm"),2),IF(AND(DAY(A2)<>DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)>MOD(B2,1),MAX(A2:B2)-MIN(A2:B2)<6),NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)&"/"&TEXT(B2-A2,"hh")&"/"&RIGHT(TEXT(B2-A2,"hh:mm"),2),IF(AND(DAY(A2)=DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)<MOD(B2,1)),NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)-1&"/"&TEXT(B2-A2,"hh")&"/"&RIGHT(TEXT(B2-A2,"hh:mm"),2),"")))))

Best
Rem0

[Rem0ram]

Note: The result shows as No. of Days / No. of hours / No. of Minutes....

dd / hh / mm

[SamT]

Thanks for posting the solution. This time I will use the Thread Tool menu to mark the thread Solved for you.