Discussions

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