Count weekdays from NOW() to a future date.

  • Thread starter Thread starter Art MacNeil
  • Start date Start date
A

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.
 
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
 
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.
 
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.
 
Hello Art,

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

Regards,
Bernd
 
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
 
Hello Art,

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

Regards,
Bernd
Click to expand...

Hello Bernd,

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

The formula always returns 14.


Art.
 
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
Click to expand...

Excellent, thanks. It works very well.
 
Back
Top