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:00 AM" and the second Date/Time field is set to 05-17-2022 12:30 AM, I want the Formula Text field label to be set to "On time". The rule for this value would be 30 minutes before and 30 minutes after the first Date/Time field. If the second Date/Time field is outside of 30 minutes after the first Date/Time field, or more than 1/2 hour after, label should be "Late".Thanks------------------------------Brian Dunk------------------------------

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------------------------------

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------------------------------

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------------------------------

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------------------------------

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------------------------------

Setting a Field value based on a Date/Time Field

MikeTamoush
Qrew Commander
3 years ago
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
  Qrew Cadet
  3 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
    Qrew Commander
    3 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
    ------------------------------

Forum Discussion

Setting a Field value based on a Date/Time Field