Count weekdays from NOW() to a future date.

[Art MacNeil]

Hello all,

Is there a formula I could use to count the number of weekdays from today
until a future date? If I use DATEDIF I can get the number of actual days,
but I'd like to get the number of weekdays from today (i.e. NOW()) to a
future date, say December 13th, 2006. Can Excel do this?


Thanks,

Art.

Windows XP
Office 2003.

 

[Biff]

Hi!

Try one of these:

=NETWORKDAYS(TODAY(),"12/13/2006")

=SUM(INT((WEEKDAY(TODAY()-{1,2,3,4,5},2)+"12/13/2006"-TODAY())/7))

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(TODAY()&":"&--"12/13/2006")),2)<6))

It'd be better to use cells to hold the start/end dates:

A1 = =TODAY()
B1 = 12/13/2006

=NETWORKDAYS(A1,B1)

=SUM(INT((WEEKDAY(A1-{1,2,3,4,5},2)+B1-A1)/7))

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))

NETWORKDAYS requires the Analysis ToolPak add-in be installed.

Biff

 

[Art MacNeil]

Success!!

=SUM(INT((WEEKDAY(TODAY()-{1,2,3,4,5},2)+"13/12/2006"-TODAY())/7)) worked.


I had to modify the date format for Canada but it works perfectly.

Thanks a lot Biff,

Art.

 

[bplumhoff]

Hello,

Why is Daniel M.'s formula forgotten by now?

Count of weekdays:
A1 Start date
A2 End date
A3 Weekday to count between A1 and A2 (1=Sunday)
Formula: =INT((A2-MOD(A2-A3,7)-A1+7)/7)
Alternative Formula (by Daniel M.): =INT((A2-WEEKDAY(A2+1-A3)-A1+8)/7)

http://www.sulprobil.com/html/date_formulas.html

Regards,
Bernd

 

[Art MacNeil]

It didn't work for me.

A3 causes the formula to die.


=SUM(INT((WEEKDAY(TODAY()-{1,2,3,4,5},2)+"13/12/2006"-TODAY())/7)) worked
after a small adjustment.


Art.

 

[bplumhoff]

Hello Art,

What exactly did you enter into A1, A2, A3, and what did you get,
please?

Regards,
Bernd

 

[bplumhoff]

Hi Art,

If you do not want to count single weekdays but all working days
without holidays:
Count of working days (Mondays through Fridays without any holidays):
A1 Start date
A2 End date
Formula: =(A2-WEEKDAY(A2,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,
WEEKDAY(A1,2))+MIN(5,WEEKDAY(A2,2))
This formula is different from Excel's built-in function (analysis
add-in). It counts from A1 24:00 until A2 24:00.

Regards,
Bernd

 

[Art MacNeil]

Hello Art,

What exactly did you enter into A1, A2, A3, and what did you get,
please?

Regards,
Bernd

Hello Bernd,

A1 = 05/09/2006 today's date
A2 - 13/12/2006
A3 = I used several numbers

The formula always returns 14.


Art.

 

[Art MacNeil]

Hi Art,

If you do not want to count single weekdays but all working days
without holidays:
Count of working days (Mondays through Fridays without any holidays):
A1 Start date
A2 End date
Formula: =(A2-WEEKDAY(A2,2)+WEEKDAY(A1,2)-A1)/7*5-MIN(5,
WEEKDAY(A1,2))+MIN(5,WEEKDAY(A2,2))
This formula is different from Excel's built-in function (analysis
add-in). It counts from A1 24:00 until A2 24:00.

Regards,
Bernd

Excellent, thanks. It works very well.