home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Media Share 9
/
MEDIASHARE_09.ISO
/
utility
/
pbaseiv.zip
/
P4SPR008.TIP
< prev
next >
Wrap
Text File
|
1991-12-16
|
2KB
|
51 lines
Sometimes I need a worksheet column to list the
weekdays--and only weekdays--in a given month. Instead of
looking at a calendar and manually entering numbers, I came
up with a trick that automatically lets my monthly forecasts
skip weekends.
[A .WK1 file with the formulae already in place can be found
in the file P4SPREAD\WKDAY.WK1 on your PowerBase *.* Volume
IV diskette. The following instructions can be used to
reproduce the worksheet manually.]
To use the technique, enter a year value such as 91 in cell
C1. Enter a month number in the range 1 to 12 in C2. You can
use other cells, but you'll have to alter the cell numbers
in the method's formulae.
Next, go to cell C4 and enter this formula:
@IF(@MOD(@DATE($C$1,$C$2,1),7)>1,1,@IF(@MOD(@DATE($C$1,$C$2,1),7)=1,2,3))
Drop down one row to C5, and enter
@IF(@MOD(@DATE($C$1,$C$2,C4+1),7)>1,1+C4,@IF(@MOD(@DATE($C$1,$C$2,C4+1),7)=1,2+C4,3+C4))
Finally, replicate cell C5 to cells C6 through C26. This
will display the weekday numbers for the month, which you
can use in expressions or simply as row labels.
To skip holidays that occur during the week, simply enter
the next day number manually. For example, if July 4 falls
on a weekday, move to the cell with the `4' and enter 5 to
replace the formula. Subsequent cells will adjust
automatically to account for the change.
Leo Lefkowits
Houston, Texas
Editor's note: Copying the second formula to every cell down
to C26 lists 23 days, the maximum number of weekdays you can
have in a month. When there are fewer than 23 workdays in a
month, or when you skip a holiday, you may get some ERRs or
an occasional invalid day of the month (such as 32) at the
bottom of the list. These are harmless and can be deleted if
they annoy you.
Title: Worksheets for Weekend Warriors
Category: SPR
Issue date: Feb 1991
Editor: Tom Swan
Supplementary files: P4SPREAD\WKDAY.WK1