How to tell if a "Month Year" string is in the past or the future

Question

What formula would you use to determine whether a string such as "September 2021" is in the past, present or future? My application needs a checkbox formula to be checked if it is in the past. For instance "August 2021" would be checked since the current month is September 2021. "January 2022" would be unchecked. "September 2021" would be unchecked.

Thank you in advance for your help!

------------------------------
Matt Makris
------------------------------

michaeltamoush · Answer

Is the current field a text string, and can you be sure it will always be in the same format, and it will be spelled the same?

ie: Will you ever have Sept. 2021 and September 2021 or perhaps Sept 21?

------------------------------
Michael Tamoush
------------------------------

mattmakris · Answer

It's a text field (multiple choice) and the spelling/format will always be constant.------------------------------Matt Makris------------------------------

markshnier__you · Answer

I think that Mike is west cost so I will take the early shift here.

var text MonthName = Trim(Left([my text string]," "));
var number YYYY = ToNumber(Right([my text string]," "));

var number MonthNumber =Case($MonthName,
"January", 1,
"February", 2,
etc
"December", 12);

var date FirstDayOfMonthStringDate = Date($YYYY, $MonthNumber, 1)

IF(
$FirstDayOfMonthStringDate < FirstDayOfMonth(Today()), "Past",
$FirstDayOfMonthStringDate = FirstDayOfMonth(Today()), "Present",
$FirstDayOfMonthStringDate > FirstDayOfMonth(Today()), "Future")

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

michaeltamoush · Answer

Thanks Mark! I lost track of time and discussion posts lol!------------------------------Michael Tamoush------------------------------

Forum Discussion

How to tell if a "Month Year" string is in the past or the future

4 Replies