Solved Show next working day (1 Viewer)

[Tophan]

Hello. I am creating an equipment maintenance schedule. I would input the date the equipment was last serviced and then input the suggested maintenance period in months e.g. 3, 6 or 12 months. I have added a simple formula that returns the next service date based on the maintenance period. The problem I am encountering is if 3 months from the service date falls on a Saturday or Sunday. I would like to add 2 days if the service date calculates to a Saturday or add 1 day if it calculates to a Sunday so that the work would be scheduled for the next Monday.

Any suggestions would be greatly appreciated.

 

[June7]

The formula is where - expression in textbox ControlSource? Do you have VBA custom function? Post your code. Use If Then or IIf() conditionals.

 

[Tophan]

Hi. I didn't use VBA. It is such a simple DB I actually added a calculated field to the table. The formula is as below

([ServicePeriod]*30)+[ServiceDate]

The form is working as it should; the only problem being that a couple of the next service dates are falling on weekends and need to be on weekdays only.

 

[theDBguy]

Hi. You could try something like this.

IIf(Weekday([ServicePeriod]*30+[ServiceDate])=1, [ServicePeriod]*30+[ServiceDate]+1, IIf([ServicePeriod]*30+[ServiceDate])=7, [ServicePeriod]*30+[ServiceDate]+2, [ServicePeriod]*30+[ServiceDate]))

(untested)
Hope that helps...

 

[Tophan]

Hi. You could try something like this.

IIf(Weekday([ServicePeriod]*30+[ServiceDate])=1, [ServicePeriod]*30+[ServiceDate]+1, IIf([ServicePeriod]*30+[ServiceDate])=7, [ServicePeriod]*30+[ServiceDate]+2, [ServicePeriod]*30+[ServiceDate]))

(untested)
Hope that helps..

Hi. You could try something like this.

IIf(Weekday([ServicePeriod]*30+[ServiceDate])=1, [ServicePeriod]*30+[ServiceDate]+1, IIf([ServicePeriod]*30+[ServiceDate])=7, [ServicePeriod]*30+[ServiceDate]+2, [ServicePeriod]*30+[ServiceDate]))

(untested)
Hope that helps...

Thank you! I tweaked it a bit and it is working. Below is how I amended it

IIf(Weekday(([ServicePeriod]*30)+[ServiceDate])=1, ([ServicePeriod]*30)+[ServiceDate]+1, IIf(Weekday(([ServicePeriod]*30)+[ServiceDate])=7, ([ServicePeriod]*30)+[ServiceDate]+2, ([ServicePeriod]*30)+[ServiceDate]))

Thanks again for your help

 

[theDBguy]

Thank you! I tweaked it a bit and it is working. Below is how I amended it

IIf(Weekday(([ServicePeriod]*30)+[ServiceDate])=1, ([ServicePeriod]*30)+[ServiceDate]+1, IIf(Weekday(([ServicePeriod]*30)+[ServiceDate])=7, ([ServicePeriod]*30)+[ServiceDate]+2, ([ServicePeriod]*30)+[ServiceDate]))

Thanks again for your help

Hi. Glad to hear you got it to work. Good luck with your project.

 

[taball0829]

In my experience, if an item actually (critically) needs an inspection or service on a given date, extending the date is not an option. If the inspection is due Saturday, the inspection needs to be done before Saturday, to use the item on Monday. Surely you use this information entered in the db. Someone must be notified that inspection or service is due soon. Would it not be much simpler to just produce a report on any given day that would cover the weekend days? For instance, on Monday, cover the next 7 or 8 days. This would show what is due on the weekend and next Monday, in case there is an item needed on Monday, that would actually be out of service on Monday. This way there is no need to figure out that the date falls on a weekend.

 

[taball0829]

Another simple solution would be to short-cycle: call a month 4 weeks; semi-annual, 26 weeks; quarterly, 12 weeks, etc. Adding 4 weeks to an item that is serviced on Monday, will be due on Monday.