Calculate difference between dates excluding weekends

**nitinjoshi123** · 05-22-2008, 10:33 PM

Hi there
I would like to know how i can calculate the difference (in days) between two dates, excluding the weekends from the calculation.
For example lets say A1 has 22 May 2008 and A2 has 27 May 2008. How do i calculate the difference between A2 and A1 so i get the answer as 3. Using Networkdates gives me 4 (it includes the start date in the calculation). I can't use datedif because sometimes my first date is smaller than the second date, in which case it gives me an error.
Also, occasionally my two dates are from two different years like it can be 22 May 2007 and 27 May 2008.
It would be really helpful if someone can help with this.
Many Thanks in advance
Cheers
Nitin

**EdMac** · 05-23-2008, 04:37 AM

try this - it does not matter which is the smaller date

Please Login or Register  to view this content.

**oldchippy** · 05-23-2008, 05:05 AM

If you haven't got the Analysis Tool-Pak installed for NETWORKDAYS, here's an alternative

Please Login or Register  to view this content.

**daddylonglegs** · 05-23-2008, 07:27 AM

Is it possible for the start date to be a Saturday or Sunday, in which case what result do you expect, e.g. for start date 17-May-2008 end date 20-May-2008?

**nitinjoshi123** · 05-25-2008, 05:11 PM

Hi
Yes!!! the start date can be Saturday or Sunday....
Thanks
Nitin

**nitinjoshi123** · 05-25-2008, 05:21 PM

HI EDMAC
This solution doesn't work if the second date is smaller than the first date. I need the negative values as well. Also, when the second date is smaller, -1 adds one more to the difference.
For example: lets take the start date as 26 may 2008
If the second date is 29 May, i need 3 as an answer
If the second date is 3 June, i need 6 as an answer
If the second date is 19 May, i need -5 as an answer
Please advice...
Thanks a lot
Cheers
Nitin

**oldchippy** · 05-25-2008, 05:35 PM

Hi,

Try this,

=IF(A1<=B1,NETWORKDAYS(A1,B1)-1,NETWORKDAYS(A1,B1)+1)

**nitinjoshi123** · 05-25-2008, 06:03 PM

HI OldChippy
Thanks!!! but this is not working if the first date is weekend

**nitinjoshi123** · 05-26-2008, 07:22 AM

Hi
I wanna calculate the difference between two dates, excluding the weekends. Networkdays works fine, but the answer is corrupted if the start date is a saturday.
My start date is sometime smaller than the last date, in which case the answer should be negative.
Many many thanks in advance
Nits

**oldchippy** · 05-26-2008, 07:51 AM

Hi,

If the start date falls on a Saturday or Sunday, do you want to calculate from the Friday or the Monday?

Same applies with the end date?

**EdMac** · 05-26-2008, 10:59 AM

If the second date is 29 May, i need 3 as an answer
If the second date is 3 June, i need 6 as an answer
If the second date is 19 May, i need -5 as an answer

This gives the answers you require

Please Login or Register  to view this content.

HTH

**nitinjoshi123** · 05-26-2008, 06:46 PM

Hi OldChippy
If the start date is saturday or sunday, i want to start from Monday. Similarly if the end date is saturday or sunday, i want to calculate upto Friday.
Thanks
Nitin

**nitinjoshi123** · 05-26-2008, 07:29 PM

Originally Posted by EdMac

This gives the answers you require

Please Login or Register  to view this content.

HTH

HI Edmac
Itried this but it is giving me name error

**daddylonglegs** · 05-26-2008, 08:22 PM

Hello Nitin,

I think I have an idea what you want to do but it helps if you can give specific representative examples, the only examples you gave have weekdays as both start and end date.

Given that you say that this formula suggested by oldchippy gives incorrect results when start date is at the weekend

=IF(A1<=B1,NETWORKDAYS(A1,B1)-1,NETWORKDAYS(A1,B1)+1)

...then I assume that if start date is Saturday and end date is 3 days later that result should be 2. For that try

=NETWORKDAYS(A1,B1)+IF(NETWORKDAYS(A1,A1)+ NETWORKDAYS(B1,B1)=2,-SIGN(NETWORKDAYS(A1,B1)),0)

**nitinjoshi123** · 05-26-2008, 10:26 PM

Hi Daddylonglegs
Fantastic!!! thanks!! it worked for me..
There is one glitch however.
If my start date is 23/05/08 (Friday) and the end date is 30/05/08 (Friday), the answer is 5, which is correct. Howver, if my start date is 23/05/08 (Friday) and my last date is 31/05/08 (Saturday), the answer it is giving is 6, when it should be 5.
Any suggestions!!!!
Thanks

**nitinjoshi123** · 05-27-2008, 06:32 PM

Giving me trouble again. I used the formula in my actual sheet and it is givingh me 2 as an anwer for start date 23/04/2008 and end date 24/04/2008, when it actually should be one.
Any suggestions

Calculate difference between dates excluding weekends

LinkBack

Thread Tools

Rate This Thread

Display

Calculate difference between dates excluding weekends

USING NETWORKDAYS if the start date is saturday

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions