Question exclude weekends and bank holidays working day criteria in hours (1 Viewer)

[lizcliff]

Hi

I will try best to explain what i need if you are able to help me; its, let say taking up a lot of my time trying to figure it myself

I have a dataset of patients being discharged with a SpellEndDateTime and a CompDateTime. Now i need to calculate whether is was 24 hours between SpellEndDateTime and CompDateTime, but if it is a non working day i need to exclude the hours.

so if a patient was discharged on a friday at 3pm i wouldnt expect them to have until saturday 3pm as the deadline for them to be completed, they would have until monday at 3pm to achieve 24 hours or if monday was a bank holiday they would have until Tuesday 3pm - do you see what i mean?

Also whilst searching for answers as well as the coding needed; i have noticed a static table of bank holiday dates would be required (for the UK)? where could i get this from or would i have to create it manually for the next couple of years?

i have to admit i am no genius in access, i can create queries some complex to me (i think) but i have no clue about sql or modules; i have looked around for answers but I'm way over my head trying to digest them.
i appreciate any help given, im sorry if i havent explained myself very well.

thank you
Liz x

 

[jdraw]

I suggest you start by identifying what are the Bank holidays in your environment.
Also you may start with (paper and pencil) and create some sort of logic tree that is specific to your needs.
I would not recommend you start by getting into the nitty gritty of vba.
If you can draw and revise as necessary a logic tree, and test various conditions, then move to implementation.

see http://www.techonthenet.com/access/functions/date/weekday.php for more info

To help you deal with Saturday and Sunday

 

[Lavinms]

Hi, I have the same issue. Did you manage to get a resolution that you could share with me please?

 

[lizcliff]

I'm afraid not.
I have passed it onto a colleague who knows how to do it, in theory but has struggled a lot and is Stil trying to develop it.
I Wil share once I know
Sorry

 

[Lavinms]

Hi, No worries, thanks for responding so swiftly. I have found a workaround on the web tonight. - A database containing a form that carries out the calculation. I have copied the form and associated module into my db and will use it as a stand alone calculator and enter the date manually until I can get my head around this!
Thanks
:banghead: