# Discussions

View Only

## Multiple if statements for date formula

• #### 1.  Multiple if statements for date formula

Posted 06-18-2019 20:18
I have service schedules in a scheduling table.

Schedules - which are separate but related records - are based on frequency (monthly, 3-monthly, 6-monthly, yearly, 3-yearly, 5-yearly).

When a higher service level is completed it means related lower service intervals have also been completed.

I have a formula that does this ... That is, when a 5-yearly service is complete the yearly, 6-monthly, etc. schedules update to show they were also done on that date.

What I need is a formula that will take the "date last service" and update the "date next service" so that related schedules should not be scheduled for the same date.

For example, if a 5-yearly service was done on 1-Jan-2000 the "date next service" of the 5-yearly service is 1-Jan-2005.

Yearly services will be scheduled for 1-Jan-2001, 1-Jan-2002, 1-Jan-2003, 1-Jan-2004 ...

But a yearly service should not be scheduled for 1-Jan-2005 ...

The formula below intends to identify whether these services fall on the same date and if so add one year so that the "date next service" becomes 1-Jan-2006.

The formula appears to work but looks clunky.

Is there a better way to write it?

If ([Date Next Schedule (this schedule)] = [Date Next Schedule: Related 5-Yearly Service],

If ([Frequency Unit] = "Year", (AdjustYear([Date Next Schedule: Related 5-Yearly Service],[Frequency No])),

(AdjustMonth([Date Next Schedule: Related 5-Yearly Service],[Frequency No]))),

//If date next schedule = date next schedule of related 5-yearly schedule
//Then adjust date next schedule by the frequency of this service level
//If frequency unit is year then adjust date next schedule by year x frequency
//If frequency unit is not year (i.e. must be month) then adjust date next schedule by month x frequency

If ([Date Next Schedule (this schedule)] = [Date Next Schedule: Related 3-Yearly Service],

If ([Frequency Unit] = "Year", (AdjustYear([Date Next Schedule: Related 3-Yearly Service],[Frequency No])),

(AdjustMonth([Date Next Schedule: Related 3-Yearly Service],[Frequency No]))),

//If date next schedule = date next schedule of related 3-yearly schedule
//Then adjust date next schedule by the frequency of this service level
//If frequency unit is year then adjust date next schedule by year x frequency
//If frequency unit is not year (i.e. must be month) then adjust date next schedule by month x frequency

If ([Date Next Schedule (this schedule)] = [Date Next Schedule: Related Yearly Service], AdjustMonth([Date Next Schedule: Related Yearly Service],[Frequency No]),

//If date next schedule = date next schedule of related yearly schedule
//Then adjust date next schedule by the frequency of this service level

If ([Date Next Schedule (this schedule)] = [Date Next Schedule: Related 6-Monthly Service], AdjustMonth([Date Next Schedule: Related 6-Monthly Service],[Frequency No]),

//If date next schedule = date next schedule of related six-monthly schedule
//Then adjust date next schedule by the frequency of this service level

If ([Date Next Schedule (this schedule)] = [Date Next Schedule: Related 3-Monthly Service], AdjustMonth([Date Next Schedule: Related 3-Monthly Service],[Frequency No]), [Date Next Schedule (this schedule)]))))))

//If date next schedule = date next schedule of related three-monthly schedule
//Then adjust date next schedule by the frequency of this service level