Datetime Formula Help

Question

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

markshnier__you · Answer

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

evanmartinez · Answer

Hi Liz,You probably want to use a formula like:If(Year(ToDate([Your DateTime Field Here])) &lt;&gt; 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 MartinezCommunity Marketing ManagerQuick Base------------------------------

lizchartrand · Answer

Ack, I was so close! Thank you Evan, this works!------------------------------Liz Chartrand------------------------------

lizchartrand · Answer

Thank you, Mark! I appreciate the help!!------------------------------Liz Chartrand------------------------------

Forum Discussion

Datetime Formula Help

Related Content

DateTime Formula

Delete Formula Button Error

Formula Assistance

Formula Url Buttons

URL formula -API_RunImport