How to create a checkbox formula that reads the current month and year from a date/time field.

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I need to create a checkbox formula field that checks only if ALL certain conditions are met. The problem I'm having is converting a date/time field to read the month and year in the checkbox formula. Here is what I have so far:

If ([Project Status]="In Progress" and

[Billing Type]="Service Deposit Project" and

(month([Last Project Notification Sent])=month(today()) 

and (year([Last Project Notification Sent])=year(today()) and

[Opt Out of Project Notifications]=false,

 true)

Everything is working except for reading that the Last Project Notification date is not reading if the notification was sent during the current month and year based on the date in the Last Project Notification Sent date/time field. It appears that I have a () problem and can't figure out where the silly thing is missing! Or maybe I just have the wrong formula. Any suggestions? 

Thanks!

Photo of Dawn

Dawn

  • 10 Points

Posted 4 years ago

  • 0
  • 1
Photo of Brendan

Brendan

  • 40 Points
Hi Dawn,

Try:
Month(ToDate([Last Project Notification Sent]))=Month(Today())
and
Year(ToDate([Last Project Notification Sent]))=Year(Today())

Let me know how it goes!

Thanks
Photo of Dawn

Dawn

  • 10 Points
Brendan! I OWE you a beer (or 3!!!). I was SO close but you blew it out of the park! THANK YOU! :)
Photo of Brendan

Brendan

  • 40 Points
Great -- Happy to hear this worked!
Photo of Dawn

Dawn

  • 10 Points
Hey Brendan, how would I adjust this portion of the formula if I wanted to change it to show that it was NOT during the current month? Thanks! Dawn
Photo of Brendan

Brendan

  • 40 Points
Hey Dawn!  I'm sure there's a better/more direct way to do this -- but what comes to mind first for me is creating a formula checkbox field that evaluates that logic:

Month(ToDate([Last Project Notification Sent]))=Month(Today())

Formula checkbox fields are powerful in QuickBase and I leverage them all the time.  The above logic would check if the criteria matches and also obviously not check if it doesn't.

You can then reference the output of that formula in your overall formula:

If([Project Status]="In Progress" and

[Billing Type]="Service Deposit Project" and

*[Month Checkbox formula] = false and

*[Year Checkbox formula] = false and

[Opt Out of Project Notifications]=false,

 true)


* = the formula checkbox fields you're adding

Hope this helps!

-Brendan
Photo of Dawn

Dawn

  • 10 Points
Brendan, that makes tons of sense to me! Thanks! Have a wonderful holiday weekend!
Photo of Brendan

Brendan

  • 40 Points
Great! Hope that works.  Enjoy the weekend!