Multiple if statements for date formula
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
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