Quickbase Discussions

 View Only
Expand all | Collapse all

Setting a Field value based on a Date/Time Field

  • 1.  Setting a Field value based on a Date/Time Field

    Posted 05-20-2022 09:03
    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
    ------------------------------


  • 2.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-20-2022 10:30
    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
    ------------------------------



  • 3.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-20-2022 11:51
    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
    ------------------------------



  • 4.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-20-2022 12:18
    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
    ------------------------------



  • 5.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-23-2022 08:55
    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
    ------------------------------



  • 6.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-23-2022 08:56
    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
    ------------------------------



  • 7.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-23-2022 10:17
    Sorry. Use Now() instead of Today() for date/time.

    If(isnull([Actual Arrival Date] and now() < [Appointment Date], "Pending",

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 8.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-23-2022 10:55
    Ok tried that and now getting this:


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



  • 9.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-23-2022 11:03
    Edited by Mike Tamoush 05-23-2022 11:05
    Sorry, I copy/pasted too fast and didn't notice you were missing a parentheses. The isnull statement needs a close parentheses. I accidentally left it off in my original response too. 

    If(isnull([Actual Arrival Date]) and now() < [Appointment Date], "Pending",

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 10.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-23-2022 12:15
    Ok I did get rid of the errors.  Now in testing it seems that "Pending" and "No Show" work with no value in the "Actual Arrival Date" Field.  However, I cannot get the "On Time" and "Late" to work by the rules provided:  See below image:

    With these dates status should be "Late".

    Brian

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



  • 11.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-23-2022 12:40
    Sorry,

    Ive been doing this quickly in between a bunch of other work. You might have to fuss with each condition now that you can see the syntax and flow. Ontime should probably be:

    [Actual Arrival Date] <= [Appointment Date] + minutes(30) and [Actual Arrival Date]>= [Appointment Date] - minutes(30), "On Time",

    but this doesnt account for if someone arrives more than 30 minutes early. You might need an early status:

    not isnull([Actual Arrival Date]) and  [Actual Arrival Date]< [Appointment Date] - minutes(30), "Super Duper Early",

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 12.  RE: Setting a Field value based on a Date/Time Field

    Posted 05-24-2022 08:11
    Mike,  thanks so much for the help.  Seems to be working now.

    Brian

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