Count weekdays from NOW() to a future date.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Art MacNeil - 01 Sep 2006 06:41 GMT

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.

Reply to this Message

Biff - 01 Sep 2006 07:32 GMT

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

> Hello all,
>
[quoted text clipped - 9 lines]
> Windows XP
> Office 2003.

Reply to this Message

Art MacNeil - 01 Sep 2006 08:25 GMT

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.

> Hi!
>
[quoted text clipped - 34 lines]
>> Windows XP
>> Office 2003.

Reply to this Message

bplumhoff@gmail.com - 01 Sep 2006 08:49 GMT

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

Reply to this Message

Art MacNeil - 01 Sep 2006 10:18 GMT

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,
>
[quoted text clipped - 11 lines]
> Regards,
> Bernd

Reply to this Message

bplumhoff@gmail.com - 01 Sep 2006 14:17 GMT

Hello Art,

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

Regards,
Bernd

Reply to this Message

Art MacNeil - 06 Sep 2006 02:17 GMT

> Hello Art,
>
[quoted text clipped - 3 lines]
> 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.

Reply to this Message

bplumhoff@gmail.com - 01 Sep 2006 15:05 GMT

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

Reply to this Message

Art MacNeil - 06 Sep 2006 02:19 GMT

> Hi Art,
>
[quoted text clipped - 10 lines]
> Regards,
> Bernd

Excellent, thanks. It works very well.

Reply to this Message