IF/AND Formula help please

David G11
David G11 ✭✭
edited 06/16/23 in Formulas and Functions

Hi all,

Looking for some help with the following IF/AND Formula please which could result in the following answers:

image.png


  1. If Task Status not equal to "Completed" then return "Currently Incomplete"
  2. If Task Status not equal to "Completed" & the task is late( End date(actual)+Time Due(Actual) are greater than their targets, return "Currently Incomplete - LATE"
  3. If the task was completed(task status is marked as "Completed") on time (in terms of date and hours), please return "Completed On Time + Timestamp the day and hour e.g. "Completed on Time - Monday 09:00")
  4. (Opposite )If the task was completed late, please return "Completed With Delay - Timestamp the day and hour" e.g Completed With Delay - Monday 11:00


Thanks so much for the help all.

Tags:
· Share on FacebookShare on Twitter

Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    I haven't tested just went off of the information above. Let me know if it doesn't work correctly and what error you are getting or if it's providing inaccurate information what is wrong and I'll create a test to see if I can fix it.

    =If(AND([Task Status]@row<>"Completed",([End date(actual0@row+[Time Due(actual)@row)>([End Date(Target)]@row+[Time due(Target)]@row),"Currently Incomplete-Late",If([Task Status]@row<>"Completed","Currently Incomplete",If(And([Task Status]@row="Completed",([End date(actual0@row+[Time Due(actual)@row)>([End Date(Target)]@row+[Time due(Target)]@row),"Completed With Delay"+" - "+[Day(Actual]@row+Time Due(Actual)@row,"Completed on Time"+" - "+[Day(Actual]@row+Time Due(Actual)@row)))

    · Share on FacebookShare on Twitter
  • David G11
    David G11 ✭✭
    https://community.smartsheet.com/discussion/comment/381057#Comment_381057

    Hi Hollie, thanks for the help.


    Unfortunatley I am still receiving the #UNPARSEABLE error.

    Here is a more detailed screenshot ( I forgot to include the Task Status column in the previous screenshot)

    image.png

    I used your formula (cleaned up any 0s with another bracket ')'):

    =IF(AND([Task Status]@row<>"Completed",([End Date(Actual)@row+[Time Due(Actual)@row)>([End Date(Target)]@row+[Time Due(Target)]@row),"Currently Incomplete-Late",IF([Task Status]@row<>"Completed","Currently Incomplete",IF(And([Task Status]@row="Completed",([End Date(Actual)@row+[Time Due(Actual)@row)>([End Date(Target)]@row+[Time Due(Target)]@row),"Completed With Delay"+" - "+[Day(Actual]@row+Time Due(Actual)@row,"Completed on Time"+" - "+[Day(Actual]@row+Time Due(Actual)@row)))


    Thanks again.

    · Share on FacebookShare on Twitter
  • David G11
    David G11 ✭✭

    Anyone else got any help please ?

    · Share on FacebookShare on Twitter
  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    I have figured out the issue but can't figure out how to turn your timestamp into a value that will add correctly. If the seconds is not important you can use the below to make a helper column for you timestamp columns. Time Stamp will need to be in 13:00 format for it to work. Then create a helper columns to combine your date and time into a value.

    Target Time Helper Column

    =VALUE(IF(LEN([Time Due(Target)]@row) = 5, LEFT([Time Due(Target)]@row, FIND(":", [Time Due(Target)]@row) - 1) + RIGHT([Time Due(Target)]@row, FIND(":", [Time Due(Target)]@row) - 1), LEFT([Time Due(Target)]@row, FIND(":", [Time Due(Target)]@row) - 1) + RIGHT([Time Due(Target)]@row, FIND(":", [Time Due(Target)]@row))))

    Actual Time Helper Column

    =VALUE(IF(LEN([Time Due (Actual)]@row) = 5, LEFT([Time Due (Actual)]@row, FIND(":", [Time Due (Actual)]@row) - 1) + RIGHT([Time Due (Actual)]@row, FIND(":", [Time Due (Actual)]@row) - 1), LEFT([Time Due (Actual)]@row, FIND(":", [Time Due (Actual)]@row) - 1) + RIGHT([Time Due (Actual)]@row, FIND(":", [Time Due (Actual)]@row))))

    Once you have that created you will need to create a helper column to combine your time and date as a value. using the below formula

    Target Time Date Helper Column

    =VALUE(YEAR(DATEONLY([End Date(Target)]@row)) + "" + IF(MONTH(DATEONLY([End Date(Target)]@row)) < 10, "0") + MONTH(DATEONLY([End Date(Target)]@row)) + IF(DAY(DATEONLY([End Date(Target)]@row)) < 10, "0") + DAY(DATEONLY([End Date(Target)]@row)) + IF([Target Time Helper]@row < 1000, "0") + [Target Time Helper]@row)

    Actual Time Date Helper Column

    =VALUE(YEAR(DATEONLY([End Date (Actual)]@row)) + "" + IF(MONTH(DATEONLY([End Date (Actual)]@row)) < 10, "0") + MONTH(DATEONLY([End Date (Actual)]@row)) + IF(DAY(DATEONLY([End Date (Actual)]@row)) < 10, "0") + DAY(DATEONLY([End Date (Actual)]@row)) + IF([Actual Time Helper]@row < 1000, "0") + [Actual Time Helper]@row)

    Final Formula referencing helper columns

    =IF(AND([Task Status]@row <> "Completed", [Actual Time Date Helper]@row > [Target Time Date Helper]@row), "Currently Incomplete - Late", IF(AND([Task Status]@row <> "Completed", [Actual Time Date Helper]@row > [Task Status]@row <> "Completed"), "Currently Incomplete", IF(AND([Task Status]@row = "Completed", [Actual Time Date Helper]@row > [Target Time Date Helper]@row), "Completed with Delay" + "-" + [Day (Actual)]@row + [Time Due (Actual)]@row, "Completed On Time" + "-" + [Day (Actual)]@row + [Time Due (Actual)]@row)))

    · Share on FacebookShare on Twitter
  • David G11
    David G11 ✭✭
    https://community.smartsheet.com/discussion/comment/381133#Comment_381133

    Hi Hollie - thanks for the help. Could I share the sheet with you please so you could add this formula in?

    · Share on FacebookShare on Twitter
  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    I'm not sure how to get it shared with me because I don't post my email on public forums but if you title your columns as I have them in the formulas you should be able to copy and paste directly from the post as I created a sheet and copied directly from the sheet to test the formulas.

    · Share on FacebookShare on Twitter
  • David G11
    David G11 ✭✭
    https://community.smartsheet.com/discussion/comment/381146#Comment_381146

    Thanks so much Hollie, I got it sorted! FYI you had spaces between columns like "Time Due (Actual)" when there was no space but I got it sorted. Thanks so much.


    The only last question I had was could we make it displayed "Currently Incomplete-Late" when End Date(Target) + Due Time(Target) is now in the past. i.e. Today's date and time is greater than end dates and time please.


    Thanks for the help again!

    · Share on FacebookShare on Twitter
  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 06/19/23

    I'm not sure how to get it based on the current date and time without manual cells that you would have to put in the current date and time but you can use the formula below if the date itself is in the past you will have to correct any spaces that were incorrect on the previous formulas.

    =IF(AND([End Date(Target)]@row < (TODAY()), [Task Status]@row <> "Complete"), "Currently Incomplete - Late", IF(AND([Task Status]@row <> "Completed", [End Date(Target)]@row < (TODAY(AND([Task Status]@row <> "Completed", [Actual Time Date Helper]@row > [Target Time Date Helper]@row), "Currently Incomplete - Late", IF(AND([Task Status]@row <> "Completed", [Actual Time Date Helper]@row > [Task Status]@row <> "Completed"), "Currently Incomplete", IF(AND([Task Status]@row = "Completed", [Actual Time Date Helper]@row > [Target Time Date Helper]@row), "Completed with Delay" + "-" + [Day (Actual)]@row + [Time Due (Actual)]@row, "Completed On Time" + "-" + [Day (Actual)]@row + [Time Due (Actual)]@row)))))))

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!