PDA

View Full Version : [SOLVED] formula to calculate difference between 2 days excluding weekend or other holidays



Rem0ram
07-06-2015, 02:17 AM
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
07-06-2015, 02:52 AM
Hi

Can you anyone advise on the query?

Best
Rem0

Rem0ram
07-06-2015, 05:33 AM
HI can any one help on this, still going in circles.

Rem0ram
07-06-2015, 06:25 AM
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
07-06-2015, 06:38 AM
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
07-07-2015, 02:27 AM
Hi

Any advice / guidance?

Still stuck on this...

mancubus
07-07-2015, 04:19 AM
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
07-07-2015, 07:20 AM
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
07-07-2015, 11:55 AM
what's that?
i see nothing new there...

Rem0ram
07-07-2015, 10:05 PM
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
07-08-2015, 12:27 AM
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
07-08-2015, 01:15 AM
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
07-08-2015, 02:48 AM
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
07-09-2015, 05:21 AM
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
07-09-2015, 05:25 AM
Note: The result shows as No. of Days / No. of hours / No. of Minutes....

dd / hh / mm

SamT
07-09-2015, 08:30 PM
Thanks for posting the solution. This time I will use the Thread Tool menu to mark the thread Solved for you.