Earliest January Date

[smods]

Hello,

Can you help please?

I have a hire date which could be any date in the past and I want to return the last instance of the 31st January in another field Unless the hire date is after the most recent 31st January date

Example 1: Current date is 31st July 2021. Hire date is 1st October 2019. Value should return as 31st January 2021.

Example 2: Current date is 10th January 2021. Hire date is 1st October 2019. Value should return 31st January 2020.

Example 3: Current date is 31st July 2021. Hire date is 15th March 2021. Value should return 15th March 2021 (earliest date January cannot be before the hire date).

Example 4: Current date 1st March 2021. Hire date is 15th April 2021. Value should return “Hire date cannot be before current date”

thank you so much in advance

 

[jasonb75]

Something like this should do it. The text description, "Hire Date" should refer to the cell with that date.

=MAX(DATE(YEAR(EOMONTH(TODAY(),-1)),1,31),"Hire Date")