Forum Discussion
Formula to automate the year to update on New Years Day 1/1/yyyy
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
2 Replies
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")
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 🙂
