I know the NETWORKDAYS function is there to calculate between two dates
excluding weekends, but I want to calculate the number of WEEKENDS between
today's date and a date in the future.

e.g. today's date is =TODAY() and a date in the future is 6th April 2007
which I have to prepare something for, so how many weekends have I got left
between the two to get the work done?

I'm sure it's simple but my brain hurts today :(

Thanks!

To calculate the number of weekends between two dates in Excel:

1. Enter the two dates in separate cells (e.g. A1 and A2).
2. Calculate the number of weekdays between the two dates using the NETWORKDAYS function:
   Formula:

   =NETWORKDAYS(A1,A2) 

3. Subtract the number of weekdays from the total number of days between the two dates using the INT function:
   Formula:

   =INT(A2-A1)-NETWORKDAYS(A1,A2) 

4. Divide the result by 2 to get the number of weekends:
   Formula:

   =INT((A2-A1)-NETWORKDAYS(A1,A2))/2 

5. Format the cell as a whole number to get the final result.

On Mon, 29 Jan 2007 04:22:00 -0800, kmcg
wrote:

I know the NETWORKDAYS function is there to calculate between two dates
excluding weekends, but I want to calculate the number of WEEKENDS between
today's date and a date in the future.

e.g. today's date is =TODAY() and a date in the future is 6th April 2007
which I have to prepare something for, so how many weekends have I got left
between the two to get the work done?

I'm sure it's simple but my brain hurts today :(

Thanks!


=A1-TODAY()-NETWORKDAYS(TODAY(),A1)+1

will give you the number of weekend days, inclusive of the starting and ending
days. In other words:

Future Dt Monday, February 12, 2007
Today Saturday, February 03, 2007
Weekend Days 4

Future Dt Monday, February 12, 2007
Today Sunday, February 04, 2007
Weekend Days 3

For the number of weekends, divide by two.




--ron

This would give you the same result without using NETWORKDAYS

=SUM(INT((8-WEEKDAY(A1+{0,1})+A1-TODAY())/7))

"Ron Rosenfeld" wrote:

On Mon, 29 Jan 2007 04:22:00 -0800, kmcg
wrote:

I know the NETWORKDAYS function is there to calculate between two dates
excluding weekends, but I want to calculate the number of WEEKENDS between
today's date and a date in the future.

e.g. today's date is =TODAY() and a date in the future is 6th April 2007
which I have to prepare something for, so how many weekends have I got left
between the two to get the work done?

I'm sure it's simple but my brain hurts today :(

Thanks!


=A1-TODAY()-NETWORKDAYS(TODAY(),A1)+1

will give you the number of weekend days, inclusive of the starting and ending
days. In other words:

Future Dt Monday, February 12, 2007
Today Saturday, February 03, 2007
Weekend Days 4

Future Dt Monday, February 12, 2007
Today Sunday, February 04, 2007
Weekend Days 3

For the number of weekends, divide by two.




--ron

Thanks to you both.

I can now work out how much of a panic I should be in!


"daddylonglegs" wrote:

This would give you the same result without using NETWORKDAYS

=SUM(INT((8-WEEKDAY(A1+{0,1})+A1-TODAY())/7))

"Ron Rosenfeld" wrote:

On Mon, 29 Jan 2007 04:22:00 -0800, kmcg
wrote:

I know the NETWORKDAYS function is there to calculate between two dates
excluding weekends, but I want to calculate the number of WEEKENDS between
today's date and a date in the future.

e.g. today's date is =TODAY() and a date in the future is 6th April 2007
which I have to prepare something for, so how many weekends have I got left
between the two to get the work done?

I'm sure it's simple but my brain hurts today :(

Thanks!


=A1-TODAY()-NETWORKDAYS(TODAY(),A1)+1

will give you the number of weekend days, inclusive of the starting and ending
days. In other words:

Future Dt Monday, February 12, 2007
Today Saturday, February 03, 2007
Weekend Days 4

Future Dt Monday, February 12, 2007
Today Sunday, February 04, 2007
Weekend Days 3

For the number of weekends, divide by two.




--ron