Feb 08 2023 05:14 PM
Comparing a column date value to a date in a formula and subsequently returning a value based on the result "Active" or "Inactive". Issue is...can't seem to find the correct formula to automate the year to update on New Years Day annually 1/1/yyyy.
Basically, I want excel to recognize the new year when the calendar changes from the last day of the year to the first of the new year.
Attached excel file to see what I am trying to achieve in cell B2
IF(A2>=DATEVALUE("1/1/2023"),"Active","Inactive")
Thank you for your help.
Scott
Feb 08 2023 10:48 PM
@Scott1417 Not sure I follow, but to avoid hard-coding a date like DATEVALUE("1/1/2023") you can perhaps use DATE(YEAR(TODAY()),1,1). The formula would then look like this:
=IF(A2>=DATE(YEAR(TODAY()),1,1),"Active","Inactive")
Feb 09 2023 05:15 PM
Riny,
Thank you for your help. The formula provide the inspiration to go further with function.
Tweaked the function b/c of some issues in the cells. The function works.
Check out the modification...
=IF(A2="<Null>","<Null>",IF(BH5<TODAY(),"Inactive","Active"))
Thank you again :)