Tricky Formula regarding Dates

[Klement20]

Hey guys,

I need serious help, I have been struggling for 2 days. This is my predicament.

I need a formula that will display "true" 40 to 0 before a specific date, it must display false for every other day of the year. So basically out of 365 days I need it to only display "true" for 40 days out of the year.

I'm using yyyy/dd/mm in my cells.

Basically I need to +1 to the persons age about 40 days before his birthday until the day of his birthday. (it's a insurance premium calculator.)

Please help, thanks so much!

 

[Andrew Poulsom]

Welcome to MrExcel.

This will return TRUE if the date in A1 is within 40 days of today's date:

=AND(A1<=TODAY(),A1>=TODAY()-40)

 

[Klement20]

Thanks so much Andrew.

Thanks so much I really appreciate it. I used the formula and it stays exactly the same. I assume it's because it also uses the year.

I need the exact same formula but excluding the year. I need to be able to use the formula for a person born in any year. I need it to return true within 40 days of the persons birthday so I hope it's possible to figure out a way to exclude the year and just work on months/days?

Is this possible?

Thank you are a star!

 

[Andrew Poulsom]

Maybe try:

=AND(DATE(YEAR(TODAY()-40)+(MONTH(A1)<=MONTH(TODAY())),MONTH(A1),DAY(A1))<=TODAY(),DATE(YEAR(TODAY()-40)+(MONTH(A1)<=MONTH(TODAY())),MONTH(A1),DAY(A1))>=TODAY()-40)