Forum Discussion

rituraj851's avatar
rituraj851
Copper Contributor
Sep 17, 2022
Solved

Date Comparison

Hello Everyone, 

I am trying to create a logic by comparing the dates. I want to be show the status as "Triggered On Time", "Triggered Late", "Past Due", "To be Triggered" and the logic I am trying to apply is 

 

  • "Triggered On Time" = If Task ID is available and To be created date is on or before Date Created from Today 
  • "Triggered Late" = If Task ID is available and To be created date is after Date Created from Today
  • Past Due = If Task ID and Date Created is not available and To be created date and has crossed Today's date
  • "To be Triggered" = If Task ID and Date Created is not available and To be created date and has not crossed Today's date

Could you please suggest on how to build this logic?

Sample Data: 

NameTo be createdDate CreatedTask IDTodayStatus
Task 109-24-202209-15-2022109-17-2022Triggered On Time
Task 209-24-202209-16-2022209-17-2022Triggered On Time
Task 308-10-202209-15-2022309-17-2022Triggered Late
Task 409-15-2022  09-17-2022Past Due
Task 509-19-2022  09-17-2022To be Triggered
Task 609-23-2022  09-17-2022To be Triggered

 

Thanks, 

Ritu Raj

BI & Data Analysis
excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Sep 18, 2022

    rituraj851 Try following formula. See the attachment.

    =IF(AND(C2="",B2<E2),"Past Due",IF(AND(C2="",B2>=E2),"To be Triggered",IF(AND(C2<>"",C2<=B2),"Triggered On Time",IF(AND(C2<>"",C2>B2),"Triggered Late","Sothething Else"))))

     

2 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Sep 18, 2022

    rituraj851 Try following formula. See the attachment.

    =IF(AND(C2="",B2<E2),"Past Due",IF(AND(C2="",B2>=E2),"To be Triggered",IF(AND(C2<>"",C2<=B2),"Triggered On Time",IF(AND(C2<>"",C2>B2),"Triggered Late","Sothething Else"))))

     

Resources