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

  • 0
  • 1
  • Question
  • Updated 11 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

  • 470 Points 250 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Try this

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

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

Casameira Reyes

  • 470 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.
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")