Formula based on status and timelines

[vicentegarzon (Vicente)]

Hi,
I have a formula for which based on the timeline end date it sets if its overdue or on time based on today´s date.

IF(DAYS({Cronograma#End},TODAY())<0,“Overdue ”,IF(DAYS({Cronograma#End},TODAY())>=0,“On Time ”,“”))

However, this means that when a project is already completed, it will still show overdue since today has passed and is not programmed to be based on the status.

In parallel I have a second column which acts based on the Project Status “Completed”.

IF({Project Status}=“ Completed ”,IF(MULTIPLY(DAYS({F4 Date Done},TODAY()),-1)<0,“Late ”,“On Time ”))

What I want is to bundle these 2 formulas into one so:
If project Status is Completed: then see if it was done late or on time, but if project status <> completed, then see if we´re already “overdue” or still “on time”.

Is this possible? Thanks for your help!

[MsquareAutomation (Msquare Automation Solutions)]

Hi@vicentegarzon ,

IF({Project Status} = “Completed”,
IF(MULTIPLY(DAYS({F4 Date Done}, TODAY()), -1) < 0, “Late”, “On Time”),
IF(DAYS({Cronograma#End}, TODAY()) < 0, “Overdue”, “On Time”))`

This formula will first check the project status. If the status is “Completed”, then it will check the date that the project was completed. If the date is in the past, then the project is considered late. Otherwise, the project is considered on time.

If the project status is not “Completed”, then the formula will check the timeline end date. If the end date is in the past, then the project is considered overdue. Otherwise, the project is considered on time.

For further implementation details kindly connect us:
MSquare Support
Visit us here
Youtube Channel

[vicentegarzon (Vicente)]

Thank you!
Found a different way but basically the same result:

IF({Project Status}<>“ Completed ”,IF(DAYS({Cronograma#End},TODAY())<0,“Overdue ”,“On Time ”),IF({Project Status}=“ Completed ”,IF(DAYS({Cronograma#End},{F4 Date Done})<0,“Done Late ”,“On Time ”)))

Thanks again!