You are here:

- Home
- Computing/Technology
- Business Software
- Using MS Access
- Workdays calculation

Advertisement

QUESTION: Hello,

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?

Thank you

Maya

ANSWER: Maya,

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

Regards,

Hi,

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.

NumberWorkDays: DateDiff("d",[ReceiptDate],[OperationDate])

This will give you the actual number of days (all days)

To the second column, add the following:

DayName: Format([ThisYear],"ddd")

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:

http://www.simply-access.com/What_is_the_IIf_Function.html

I hope this is clear and good luck.

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 9 | Politeness = 10 |

Comment | Thank you Julie, I will try to apply your solution. Regards, Maya |

Using MS Access

Answers by Expert:

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.**Education/Credentials**

Self taught in Microsoft Access.
Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.