asked on VBA - Calculating a date that is a weekday and not a holiday
Hi Guys and Gals
I'm looking to create a custom function that I pass a date to and an integer. I want to add x days onto a date and the result date is not to be a holiday or a weekend. I've got so far myself but for some reason my function lookup is returning a 'false' when its testing if its a holiday, but only on some entries.
The holidays table is a simple 3 column table with ID, Description and Holiday (This is a date/time field).
In my test, I put Xmas day in as the start date and put holidays in for xmas, boxing day, and 1st - 4th Jan. Yet my output date comes back as 4th Jan which is a holiday.... I can't figure out why it won't recognise this is a holiday.
Attached is the database - what am I missing?
James
Trackers-VBA.accdb
I'm looking to create a custom function that I pass a date to and an integer. I want to add x days onto a date and the result date is not to be a holiday or a weekend. I've got so far myself but for some reason my function lookup is returning a 'false' when its testing if its a holiday, but only on some entries.
The holidays table is a simple 3 column table with ID, Description and Holiday (This is a date/time field).
In my test, I put Xmas day in as the start date and put holidays in for xmas, boxing day, and 1st - 4th Jan. Yet my output date comes back as 4th Jan which is a holiday.... I can't figure out why it won't recognise this is a holiday.
Attached is the database - what am I missing?
James
Trackers-VBA.accdb
VB ScriptMicrosoft Access
Last Comment
Delerium1978
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I'm from the UK. Used that format line code and worked a charm. I had a hunch it was something format related but there's no clear documentation that states that you need to do this anywhere (that I found anyway).
Nice work anyway. Thanks :)
James