Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easiest way to match against array of holidays? XL07
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easiest way to match against array of holidays? XL07
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 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilter-criteria with VBA in XL07 | Excel Programming | |||
queries updating much slower in xl07? | Excel Discussion (Misc queries) | |||
Match using array of column and row references to match with | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) |