Question: Trying to find 1st Workday of Month <excluding Holidays

  • Thread starter Thread starter Graystar
  • Start date Start date
G

Graystar

****************************************************************
Part 1
****************************************************************

I have a sheet that I plug in the weekday date, such as today "3/24/04" @
$F$1.

Question: I'm trying to make a function to find the 1st Workday of Month
<excluding Holidays>
Using a Holidays.xls source file, but have little clue as to how to do the
compare.

Here is what I have so far: @ C9
=IF(TESTWD1,EOMONTH($F$1,-1)+1,EOMONTH($F$1,-1)+2)
where TESTWD1 is a Name Defined function:
TESTWD1=IF(MONTH(IF(ISNUMBER($C9),IF(WEEKDAY($C9+1,2)=6,IF(WEEKDAY($C9+2,2)=
7,$C9+3,$C9+2),$C9+1),"
"))=MONTH($F$1),IF(ISNUMBER($C9),IF(WEEKDAY($C9+1,2)=6,IF(WEEKDAY($C9+2,2)=7
,$C9+3,$C9+2),$C9+1)," "),"")

Result "2-Feb"
using the Sunday=1, Saturday=7 calendar

I incremented the following days with another equation that I can supply if
needed.

The results look like this <leaves out weekends>:
PrevMWkDay Weekday# CurrMonthWkDay
2-Feb 1 1-Mar
3-Feb 2 2-Mar
4-Feb 3 3-Mar
5-Feb 4 4-Mar
6-Feb 5 5-Mar
9-Feb 6 8-Mar
10-Feb 7 9-Mar
11-Feb 8 10-Mar
12-Feb 9 11-Mar
13-Feb 10 12-Mar
16-Feb 11 15-Mar
17-Feb 12 16-Mar
18-Feb 13 17-Mar
19-Feb 14 18-Mar
20-Feb 15 19-Mar
23-Feb 16 22-Mar
24-Feb 17 23-Mar
25-Feb 18 24-Mar
26-Feb 19 25-Mar
27-Feb 20 26-Mar
21 29-Mar
22 30-Mar
23 31-Mar



All I have to do is get the 1st day and the rest follow (for comparisons,
etc)

Any ideas?

****************************************************************
Part 2
****************************************************************

Also...Path Variables in equations?
Is it possible to take a found date and insert it into the data pull path of
an equation?

Such as this long string (the 1st line can be ignored.)
='C:\Documents and Settings\BIG HONKING 26CHAR PATH\Revenue\
[Insert ONE MONTH Decremented Path variable HERE]\[
(PrevMWkDay from CurrMWkDay Path variable HERE) daily.xls]TAB'!N4

Here is how it works.
General path\Specific month path\daily file path\Tab!data cell
Example
General path\2004-2 (February)\2004-2-29 daily.xls\Revenue!N4

Where 2004-2 (February) is the store directory
2004-2-25 daily.xls is the data file of the

I'm just wondering if it's possible to do or if there is a better solution,
as the above list
only exists <days numbered> for the given month.
 
Hi Graystar!

Try:

=WORKDAY(DATE(YEAR(A1),MONTH(A1),0),1,Holidays)

WORKDAY is an Analysis ToolPak function.
Holidays is a range that contains the dates of Holidays.

Note that Excel treats the 0th of a Month as being the last day of the
preceding month. So if we add one working day we get the first working
day of the month in A1.

Keep distinctly different questions to different posts.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Graystar!

Re: Fortunately I already worked out this answer

Sorry we were too slow <vbg>

Seriously though, it is always best if you can figure it out.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top