Help with IF formula comparing current date to current month?

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

I need to construct a formula that basically says:  If the field [Recurrence] = "Every Month-End Only", AND the current date falls within the month Jan, Feb, Apr, May, July, Aug, Oct, or Nov, then set this field [Status] to Active, else set this field [Status] to Inactive.  Is this possible?

Photo of Jennifer

Jennifer

  • 10 Points

Posted 4 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
No problem,


IF(
[Recurrence] <> "Every Month-End Only", "InActive",
Case(Month(Today()),
1, "Active",
2, "Active",
4, "Active",
5, "Active",
7, "Active",
8, "Active",
10, "Active",
11, "Active","InActive"))





AND the current date falls within the month Jan, Feb, Apr, May, July, Aug, Oct, or Nov, then set this field [Status] to Active, else set this field [Status] to Inactive.  Is this possible?
Photo of Jennifer

Jennifer

  • 10 Points
Thanks Mark!  I actually have additional criteria that I would need to add to this “Status” field and I thought if I got this part down I could figure out the rest, but I’m still not sure where to go from here.  Here is exactly what I am looking to do:

If the field [Recurrence] = "Every Month-End Only", AND the current date falls within the month Jan, Feb, Apr, May, July, Aug, Oct, or Nov, then set this field [Status] to Active; or

If the field [Recurrence] = "Every Quarter-End Only", AND the current date falls within the month Mar, June, Sept, or Dec, then set this field [Status] to Active; or

If the field [Recurrence] = "One-Time Only", AND the field [Production Run Date] is after today’s date, then set this field [Status] to Active; or

If the field [Recurrence] = "Every January", AND the current date falls within the month Dec, then set this field [Status] to Active; or

If the field [Recurrence] = "Every February", AND the current date falls within the month Jan, then set this field [Status] to Active; or

If the field [Recurrence] = "Every March", AND the current date falls within the month Feb, then set this field [Status] to Active; or

If the field [Recurrence] = "Every April", AND the current date falls within the month Mar, then set this field [Status] to Active; or

If the field [Recurrence] = "Every May", AND the current date falls within the month Apr, then set this field [Status] to Active; or

If the field [Recurrence] = "Every June", AND the current date falls within the month May, then set this field [Status] to Active; or

If the field [Recurrence] = "Every July", AND the current date falls within the month June, then set this field [Status] to Active; or

If the field [Recurrence] = "Every August", AND the current date falls within the month July, then set this field [Status] to Active; or

If the field [Recurrence] = "Every September", AND the current date falls within the month Aug, then set this field [Status] to Active; or

If the field [Recurrence] = "Every October", AND the current date falls within the month Sept, then set this field [Status] to Active; or

If the field [Recurrence] = "Every November", AND the current date falls within the month Oct, then set this field [Status] to Active; or

If the field [Recurrence] = "Every December", AND the current date falls within the month Nov, then set this field [Status] to Active

, else set this field [Status] to Inactive.

I'm sure I can figure it out once I learn how to nest the IF statements in to one formula and incorporate the AND statements as well.  Any guidance you can offer is appreciated!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Fortunately for you I discovered the Big Bang Theory late in life and I'm making up for it now by watching 8 seasons from the beginning at the rate of 2 or 3 a night.  I can also multi task.

I find in these complicated logic formuals, often its best to leave elegance aside and go for a formula which has no nesting, but instead just grinds through each condition / result in a way that is highly human readable.


Try this.  If there are syntax errors you will need to post your formula and the error.  I use a formula variable for the Month(Today())) as it gets used over and over and over .....




var number MT = Month(Today());


IF(

[Recurrence]= "Every Month-End Only" and $MT=1, "Active",


[Recurrence]= "Every Month-End Only" and $MT=2, "Active",

[Recurrence]= "Every Month-End Only" and $MT=4, "Active",

[Recurrence]= "Every Month-End Only" and $MT=5, "Active",

[Recurrence]= "Every Month-End Only" and $MT=7, "Active",

[Recurrence]= "Every Month-End Only" and $MT=8, "Active",


[Recurrence]= "Every Month-End Only" and $MT=10, "Active",

[Recurrence]= "Every Month-End Only" and $MT=11, "Active",


[Recurrence] = "Every Quarter-End Only" and $MT =3, "Active",
[Recurrence] = "Every Quarter-End Only" and $MT =6, "Active",
[Recurrence] = "Every Quarter-End Only" and $MT =9, "Active"
[Recurrence] = "Every Quarter-End Only" and $MT =12, "Active"

[Recurrence] = "One-Time Only" and [Production Run Date] > Today(), "Active"
[Recurrence] = "Every January" and $MT = 12, "Active",
[Recurrence] = "Every February" and $MT = 1, "Active",
[Recurrence] = "Every March"
and $MT = 2, "Active",
[Recurrence] = "Every April"
and $MT = 3, "Active",
[Recurrence] = "Every May"
and $MT = 4, "Active",
[Recurrence] = "Every June"
and $MT = 5, "Active",


[Recurrence] = "Every July" and $MT = 6, "Active",
[Recurrence] = "Every August"
and $MT = 7, "Active",
[Recurrence] = "Every September"
and $MT = 8, "Active",
[Recurrence] = "Every October"
and $MT = 9, "Active",
[Recurrence] = "Every November"
and $MT = 10, "Active",
[Recurrence] = "Every December"
and $MT = 11, "Active", "Inactive")
Photo of Jennifer

Jennifer

  • 10 Points
This worked perfectly!  I just had to add in a few missing commas and it worked.  Thank you so much for you help, Mark!  Seeing how this is laid out will definitely help me to be able to figure out a few other formulas that I'm working on :)

P.S.  I LOVE Big Bang Theory!  I never seem to catch the new ones, but love watching the re-runs :)