Date IF formula based Today() and date range.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
HI,

I am using the below formula to place a record into a stage depending on a date field. This was working really well until I needed to add another status in between two existing statues. I have highlighted the new part in bold. The result I am getting is it ether goes strait through to the  Over Due Status or if I reverse the < I only get Return Window. I am assuming I have just made a simple error but cant work this one out.

If(

IsNull([Install Date]) and Trim([Serial Number])="", "Requested",

not IsNull([Return Date]), "Returned",


Trim([PO])<>"", "Sold",


IsNull([Install Date]) and Trim([Serial Number]) <>"", "Assigned",


[Company Owned End] > Today(), "Installed",


[Company Owned End] <Today() and [Company Owned End]>Today() + Days (14) and IsNull([Return Date]) and Trim([PO])="" and Trim([RMA Number])="", "Return Window",


[Company Owned End] < Today() + Days (14) and IsNull([Return Date]) and Trim([PO])="" and Trim([RMA Number])="", "Over due",


[RMA Number]<>"", "Collection started",


"Check")
Photo of James

James

  • 172 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,904 Points 50k badge 2x thumb
Well right off the bat, I see that [Company Owned End] can never simultaneous be both less than today, which means in the past, and also greater than today plus something, ie in the future.  

Although I did just see a movie "Arrival" which gets into the area of time travel and for our heroine, it was possible for her.