Date exclude weekdays

[LukePW]

Hi,

Creating a cell range in which I enter a single date at the top of a column.
I then fill 15 cells underneath this single cell ( a column) with a formula:
=A6+1

which gives me the following day. It gives me the weekends though.... I
know I can exclude weekends by using the fill function and then choosing
weekdays. But I have to do this everytime... Is there a way to create a
formula which will only fill my range with the weekdays without having to
come and fix this everytime?

Thanks!

Luke

 

[JE McGimpsey]

Take a look at WORKDAY() function in XL Help. The function is part of
the Analysis Toolpak Add-in.

 

[Sandy Mann]

Take a look at WORKDAY() function in XL Help. The function is part of
the Analysis Toolpak Add-in.

or if you don't want to install the Analysis Toolpak Addin:

=A6+1+(WEEKDAY(A6+1,3)>4)*(7-WEEKDAY(A6+1,3))

copied down as far as you need

 

[Tom Ogilvy]

for this particular case, wouldn't it be just as easy to skip the weekend?

=A6+1+(WEEKDAY(A6,1)=6)*2

Of course JE's suggestion allows consideration of holidays.

 

[Sandy Mann]

=A6+1+(WEEKDAY(A6+1,3)>4)*(7-WEEKDAY(A6+1,3))

Of course if I were to think about it more clearly

=A6+1+(WEEKDAY(A6+1,3)=5)*2

is all that is required

Sandy

 

[Sandy Mann]

for this particular case, wouldn't it be just as easy to skip the weekend?

=A6+1+(WEEKDAY(A6,1)=6)*2

Of course JE's suggestion allows consideration of holidays.

Great minds? <g>

Regards

Sandy

 

[LukePW]

Thanks Sandy - will try that

or if you don't want to install the Analysis Toolpak Addin:

=A6+1+(WEEKDAY(A6+1,3)>4)*(7-WEEKDAY(A6+1,3))

copied down as far as you need

 

[LukePW]

Thanks !

Take a look at WORKDAY() function in XL Help. The function is part of
the Analysis Toolpak Add-in.

 

[LukePW]

Hello Sandy,

I have tried your formula but was not able to make this work. I get an
error ($name). Of course, it might not help that my Excel is in French
(although we seem to use the same lingo). Any ideas?

Luke

 

[Sandy Mann]

Sorry Luke. Your Excel is obviously telling you that it does not recognise
the WEEKDAY function but I have no idea what the French function is called.
Perhaps one of the multi-linguists will jump in and advise you. In my Excel
when it doesn't recognise a function name is gives the error message #NAME?
so there are subtle differences but I would expect the syntax to be similar.
Try looking through the functions in the fx dialog box to see it you can
find the WEEKDAY equivalent.

Hope you get the help you need

Regards

Sandy