Skip to main content

Multiple Nested IF Statement - calculate Past Due based on if Start Date has yet to begin based on Todays date and if End Date is greater than today's date

  • March 13, 2020
  • 2 replies
  • 58 views
S
Forum|alt.badge.img

Greeting and wow my second post in two consecutive days, yikes.

First, my apologies if this has been answered, i am sure the logic was but applying it to my specific situation is my challenge.

Objective: Create a Multiple Nested IF Statement for ProjectStatus and StartDate The Formula field will be called StartDateDays.

If ProjectStatus (single select field type) is “In Progress” calculate differences between StartDate and Today’s date {Today}. If False, leave StartDateDays blank or null since project has yet to start. False would be “Not Started” or “On Hold” or “Completed”

If ProjectStatus is True (“In Progress”), then calculate difference between StartDate and Today’s Date using: DATETIME_DIFF(StartDate, TODAY(), ‘days’))

If difference between Start Date and Today’s Date is a negative number in the StartDateDays, that means the project has commenced. If a positive number is produced, that mean Project Manager made a mistake selecting Project is in progress yet the Start Date is greater than Today’s date (Date in the Future).

Any thoughts on how best to construct the formula for the StartDateDays for two conditions from two fields?

    This topic has been closed for replies.

    2 replies

    S
    Forum|alt.badge.img
    • Scott_Burton
    • Author
    • Participating Frequently
    • March 13, 2020

    After reading this regarding a two condition IF statement for Project Status where Project State is ‘In Progress’ and Difference between Start Date and Today’s Date is Negative, = Project in Progress. If difference between Start Date and Today’s Date is Positive = Project has yet to start as Start Date is in future.

    Well that logic would apply for End Date. I cannot assume because the difference between EndDate and Today’s date being negative the project is past due. I need the other condition regarding Project Status. For example, if Project Status is Completed and in the Table the End Date is older than the Current Date, the diff will be a negative value but that does not mean it is Past Due. So i need a Two Condition IF statement for End Date and Project Status.

    So any assistance greatly appreciated.

    S
    Forum|alt.badge.img
    • Scott_Burton
    • Author
    • Participating Frequently
    • March 13, 2020

    After reading this regarding a two condition IF statement for Project Status where Project State is ‘In Progress’ and Difference between Start Date and Today’s Date is Negative, = Project in Progress. If difference between Start Date and Today’s Date is Positive = Project has yet to start as Start Date is in future.

    Well that logic would apply for End Date. I cannot assume because the difference between EndDate and Today’s date being negative the project is past due. I need the other condition regarding Project Status. For example, if Project Status is Completed and in the Table the End Date is older than the Current Date, the diff will be a negative value but that does not mean it is Past Due. So i need a Two Condition IF statement for End Date and Project Status.

    So any assistance greatly appreciated.


    The best thing of writing down your question is that during the process, it is amazing that you can actually figure out the answer to your question, which i did :slightly_smiling_face:

    Goal:
    Issue: Create a nested IF statement based on two different fields. First, i had to indicate the project was in progress. I have a list of projects that include the actual start and end times and those that have been completed. So i did not want to identify an End Date where the project was completed.

    I just wanted to see which projects that are in progress are past due.

    Solved: Condition IF statement where IF Project is in Progress calculate difference between Today’s Date and End Date.
    IF(
    {Project Status}=‘In Progress’,
    IF(
    DATETIME_DIFF(TODAY(),{End_Date},‘d’)>14,
    “ :x: Past Due over 2 weeks”,
    IF(
    DATETIME_DIFF(TODAY(),{End_Date},‘d’)>7,
    ‘ :alarm_clock: Overdue by more than one week’,
    IF(
    DATETIME_DIFF(TODAY(),{End_Date},‘d’)<7,
    ‘ :running_man: Overdue less than one week’
    )
    )
    )
    )

    Terms & ConditionsAccessibility statement