Easiest way to match against array of holidays? XL07

[ker_01]

I'm setting up a workbook that will load all of the files in a 'dropzone' LAN
folder and process it in Excel (mostly automated now, and I hope to make it
fully automated).

Some of the automated reports that generate my source files run 7 days a
week, but I'm only interested in workdays. So, I've used weekday(targetdate)
on dates inside the files to determine when they were run, and ignore any
that return 1 or 7 (system setting for Sun and Sat).

I'd like to also set it up to ignore company holidays, and I'm trying to
figure out the cleanest way to do it. I've used Networkdays on other
projects, which guides my approach toward creating an array of the holidays
to compare against. The question is how to approach it; I looked for an
equivalent of day() that would give the day number from 1-365 instead of just
the 1-31 month day, but couldn't find a function to do that. 1-365 isn't
intuitive (what day is July 4th? I'd have to add it all up to know). The
numeric date (40232 for today) also isn't intuitive- I'd also have to add up
the days to figure out each holiday. I think there must be an easier way, but
am having trouble thinking of it. Maybe an array of the date strings ("July
4,2010",etc) then convert the date in the raw data file to the same string
format for comparison?

I'm open to ideas- I suspect I'm overthinking this and there must be a
simple solution.

Thanks,
Keith

 

[OssieMac]

Hi Keith,

I should think you can use the Workday function and compare it against
today's date. On a worksheet it would be like the following where $E$1:$E$5
is the holiday list.

Basically it is testing if 1 day after yesterday is a workday. Reason for 1
day after yesterday is because if you use zero for the days parameter, it
always returns the startdate parameter.

=IF(WORKDAY(TODAY()-1,1,$E$1:$E$5)=TODAY(),"Workday","Not Workday")

In VBA something like the following example.

Dim dateToTest As Date

dateToTest = Date 'Today's date

With Sheets("Sheet1")
If WorksheetFunction.WorkDay _
(dateToTest - 1, 1, _
.Range("E1:E5")) = Date Then

MsgBox Format(dateToTest, _
"ddd dd mmm yyyy") & " is a workday"
Else
MsgBox Format(dateToTest, _
"ddd dd mmm yyyy") & " is NOT a workday"
End If
End With