Forum Discussion

LizChartrand's avatar
LizChartrand
Qrew Cadet
5 years ago

Datetime Formula Help

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
------------------------------
Formulas and functions

4 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    5 years 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
    ------------------------------
    • LizChartrand's avatar
      LizChartrand
      Qrew Cadet
      5 years ago
      Thank you, Mark! I appreciate the help!!

      ------------------------------
      Liz Chartrand
      ------------------------------
  • EvanMartinez's avatar
    EvanMartinez
    Qrew Elite
    5 years 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
    ------------------------------
    • LizChartrand's avatar
      LizChartrand
      Qrew Cadet
      5 years ago
      Ack, I was so close! Thank you Evan, this works!

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