Discussions

Expand all | Collapse all

Datetime Formula Help

  • 1.  Datetime Formula Help

    Posted 15 days ago
    Hello,
    I am attempting to set up a formula in Advanced Table Settings, that would generate a warning message if [Lease Processed], a DateTime field, was accidentally entered with a date outside of the current year.

    We have had some data entry issues, particularly during the new year, and we want to be able to catch those clerical errors. We were seeing a lot of 2019 or 2002 instead of 2020. With that said, I'd like the formula to be centered on the current year, but not specifically 2020. We don't want to hinder our team once we are done with this god-forsaken year ;).

    I'm having syntax issues and it may be due to the fact that it is a DateTime field, not just a date field. It's been a while since I wrote a formula, so I figured I'd ask the pros and stop wasting time.
    Any suggestions?
    Thanks!

    ------------------------------
    Liz Chartrand
    ------------------------------


  • 2.  RE: Datetime Formula Help

    Posted 15 days ago
    Hi Liz,

    You probably want to use a formula like:

    If(Year(ToDate([Your DateTime Field Here])) <> Year(Today()), "Your Warning Message Here")

    This will check to see if your DateTime field is not equal to the current year and if it is not then they should get the warning message you set up. I hope that is helpful Liz.

    ------------------------------
    Evan Martinez
    Community Marketing Manager
    Quick Base
    ------------------------------



  • 3.  RE: Datetime Formula Help

    Posted 15 days ago
    Ack, I was so close! Thank you Evan, this works!

    ------------------------------
    Liz Chartrand
    ------------------------------



  • 4.  RE: Datetime Formula Help

    Posted 15 days ago
    I suggest that you enable data validation on the Advanced Properties of the table.

    Then we make a formula that calculates to an error message.  If the error message is null, then the record will save.  Else it will display the error message.  This also works in Grid Edit or importing or any APIs whcih create records, so it is rock sold.

    The formula would be

    IF(Year(Today()) <> Year(Lease Processed],"Sorry, but the Lease Processed Year is not in the Current Year.")

    Now, you may want to think about what will happen right near year end, say January 2nd where staff are still entering last year's records.  Decide if you need to allow some wiggle room with an override checkbox. 

    IF(Year(Today()) <> Year(Lease Processed] and [Allow non-current Year]=false,
      "Sorry, but the Lease Processed Year is not in the Current Year.")




    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Datetime Formula Help

    Posted 15 days ago
    Thank you, Mark! I appreciate the help!!

    ------------------------------
    Liz Chartrand
    ------------------------------