I need a formula for a certain time of the month to equal yes or no.

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
I need a formula stating if date created occurred between the  1st and the 14th, yes is the value in the field. 
Photo of Casameira Reyes

Casameira Reyes

  • 380 Points 250 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
Try this

var number DayNumber = Day(ToDate([Date Created]));

IF($DayNumber >=1 and $DayNumber <= 14,"yes") 
Photo of Casameira Reyes

Casameira Reyes

  • 380 Points 250 badge 2x thumb
Thank you, it worked. How can I add if it is from the 15th to the end of the month, it is a "no"? I thought I had the option for a default to No, but it doesn't allow me to do that.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
You can change the IF to read like this.

IF($DayNumber >=1 and $DayNumber <= 14,"yes", "no") 

but that would mean that if that field is on the form in Add modem, then the [Date Created] field will be null as the record has not yet been created and yet the value would calculate to no and would then maybe change to yes when saved, which would be confusing for users.


So alternative is to change it to this

IF(
$DayNumber >=1 and $DayNumber <= 14,"yes",
$DayNumber >=15n "no")

That way it would be blank until the record was initially saved.  

If you want to get fancier with the formula there is also a way to make it work in Add mode by changing the whole formula to this

var number DayNumber = IF(IsNull([Date Created], Day(Today()),  Day(ToDate([Date Created])));

IF($DayNumber >=1 and $DayNumber <= 14,"yes", "no")