Multiple if statements for date formula

  • 0
  • 1
  • Question
  • Updated 4 weeks ago
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





Photo of Andrea Pahor

Andrea Pahor

  • 386 Points 250 badge 2x thumb

Posted 4 weeks ago

  • 0
  • 1

Be the first to post a reply!