Forum Discussion

BrianDunk's avatar
BrianDunk
Qrew Cadet
2 years ago

Setting a Field value based on a Date/Time Field

I am trying to right a formula that will compare two Date/Time fields and set the text formula fields value based on the two values.  For example, if the first Date/Time field is set to "05-17-2022 12...
Formulas and functions
MikeTamoush's avatar
MikeTamoush
Qrew Commander
Untested by try:

If(
 [Date Time Field 2] > [Date Time Field 1] + minutes(30) or  [Date Time Field 2] < [Date Time Field 1] - minutes(30), "Late", "On time")

------------------------------
Mike Tamoush
------------------------------
BrianDunk's avatar
BrianDunk
Qrew Cadet
2 years ago
Thanks Mike,  that seems to work.  I thought I could get away with giving a subset of my formula and that would get me going.  That is not the case.  I should have presented all the rules initially as I seem to be getting boolean errors.  Here is a more clear definition of my labels
"Pending" = Date/Time 2 blank and today is before Date/Time 1.
"On Time" = Date/Time 2 is 30 minutes before or 30 minutes after Date/Time 1
"Late" = Date/Time 2 is 30 minutes or more after Date/Time 1.
"No Show" = Date/Time 2 is blank and today is after Date/Time 1.

Thanks again!

------------------------------
Brian Dunk
------------------------------
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    2 years ago
    If you post your code we can look, but something like this.

    If(
     isnull([Date2] and today()<[Date1], "Pending",
    [Date2] <= [Date1] + minutes(30) or [Date2]>= [Date1] + minutes(30), "On Time",
    [Date2] > [Date 1] + minutes(30) or [Date2] < [Date 1] - minutes(30), "Late",
    isnull([Date2]) and today()>[Date1], "No Show")

    Untested....

    ------------------------------
    Mike Tamoush
    ------------------------------
    • BrianDunk's avatar
      BrianDunk
      Qrew Cadet
      2 years ago
      Hey Mike, I added actual field names and keep gettng a syntax error.  This is what my formula looks like to this point.
      As you can see the first line seems to have an issue and when I try to save I get the "Formula Syntax Error" pop up window.

      Any suggestions on how to fix?
      Thanks again,
      Brian

      ------------------------------
      Brian Dunk
      ------------------------------
      • BrianDunk's avatar
        BrianDunk
        Qrew Cadet
        2 years ago
        If(isnull([Actual Arrival Date] and today() < [Appointment Date], "Pending",
        [Actual Arrival Date] <= [Appointment Date] + minutes(30) or [Actual Arrival Date]>= [Appointment Date] + minutes(30), "On Time",
        [Actual Arrival Date] > [Appointment Date] + minutes(30) or [Actual Arrival Date] < [Appointment Date] - minutes(30), "Late",
        isnull([Actual Arrival Date]) and today()>[Appointment Date], "No Show")

        ------------------------------
        Brian Dunk
        ------------------------------