Date Calculations - Week Days

I have the following 3 fields, in tblContacts

C_daterequest_rec (Log Date) = Default to Date() when records is created
C_DatetoCompleteBy (Completed Date) = Log Date + 20 (Weekdays Only)
C_daystocomplete (Days to Complete) = Completed Date - Date()

I cannot seem to get the calcualation for the 'completed Date' to work.

I want it to be an actual date stored in my contacts table, but it to be calculated
after the logdate is set on creation of the log date. I cannot just add 20 days, as i want it
to add 20 weekdays (working days) to the log date, so that it gives a date the request needs
to be completed by.

Then the days to complete calculates the remaining days to the deadline.

Any help on getting the complete date calculation working would be greatly appreciated...

thanks

Mezza1

Hi Mezza1

20 workdays will include 4 weekends for a total of 28 days

C_DatetoCompleteBy = DateAdd("d",28,[C_daterequest_rec])

To get the difference in number of days from current date to completion date

C_daystocomplete = DateDiff("d",Date,[C_DatetoCompleteBy])

Bear in mind that DaystoComplete will include weekend days, so if you want to exclude weekend days from the calculations you will need to develop a select case statement to evaluate the number of total days and then subtract the number of weekend days from that total. Example if the number of DaystoComplete was between 7 and 11 then you would subtract 2 for one complete weekend.

lwells