Trying to determine duration between 2 date fields to see if it was completed on time, get type mismatch?

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

 Trying to determine duration between 2 date fields to see if it was completed on time, Like:  if([Field1]-[Field2] >0, "Overdue","On-Time")  Get the type mismatch error when I save, any tips?

Photo of Bob

Bob

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
All these should work (untested):

If(ToWeeks([Field1]-[Field2]) > 0, "Overdue","On-Time")
If(ToDays([Field1]-[Field2]) > 0, "Overdue","On-Time")
If(ToHours([Field1]-[Field2]) > 0, "Overdue","On-Time")
If(ToMinutes([Field1]-[Field2]) > 0, "Overdue","On-Time")
If(ToSeconds([Field1]-[Field2]) > 0, "Overdue","On-Time")
If(ToMSeconds([Field1]-[Field2]) > 0, "Overdue","On-Time") 

[Field1]-[Field2] is a duration not a number to you have to call one of the conversion functions co convert a duration to a number before the test is performed.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
You can try this

if([Field1]-[Field2] > Days(0), "Overdue","On-Time")

Since you are subtracting dates, the result will be a Duration.  So you need to compare it to a Duration.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
You got me there. I updated my answer.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
Better still:

If( [Field1] > [Field2] , "Overdue" , "On-Time") 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Ok, I agree.