Using MS Access/Workdays calculation
I have for a delivery:
- Receipt date
- Operation date
I need to calculate the workdays between these two days. I've searched in Access but couldn't find this built in function. Can you help me?
Thanks for the question. My apologies for the delay. Do you need to include public holidays, or are you happy with just counting Monday to Friday, whether or not it is a public holiday.
---------- FOLLOW-UP ----------
QUESTION: Thanks for your prompt reply.
No, I just need to exclude Sundays
There is not an easy built in function that I know of. I have tried to avoid writing code for you. Therefore I have solved this problem by using a query and the IIF function.
To do what you ask, create a query based on the table with your two fields above.
Add the following to the first column in your query.
This will give you the actual number of days (all days)
To the second column, add the following:
This will display the actual day name.
Then finally, this is the biggy, in the third column add the following:
ActualWorkDays: IIf(([DayName]="Mon" And [NumberWorkdays] Mod 7<=6) Or ([DayName]="Tue" And [NumberWorkdays] Mod 7<=5) Or ([DayName]="Wed" And [NumberWorkdays] Mod 7<=4) Or ([DayName]="Thu" And [NumberWorkdays] Mod 7<=3) Or ([DayName]="Fri" And [NumberWorkdays] Mod 7<=2) Or ([DayName]="Sat" And [NumberWorkdays] Mod 7<=1) Or ([DayName]="Sun" And [NumberWorkdays] Mod 7<=0),[NumberWorkdays]-Int([NumberWorkDays]/7),[NumberWorkDays]-(Int([NumberWorkDays]/7))-1)
What this does:
Based on the day of the week, we need to determine how many Sundays there are and subtract that number from the total number of days.
I used the 'Mod' function to tell me how many days are left when the total number of days is divided by 7. Depending on the number i.e. for Monday it is <= 6, then I know there is not an additional Sunday, so the result will be:
The Total Number of Days subtracting the total number of days divided by 7 (using the 'Int' function, we get the whole number)
But if the 'Mod' number for Monday was > 6 (i.e. 7 is the only option), then we know that there is an extra Sunday, so that has to be taken into consideration.
If you wish to know more about the IIF function:
I hope this is clear and good luck.