Formulae for month and year

[newtoexcel24]

Hi there,
I need to help a friend and give her a report which will help her not forget when to contact customers to renew their maintenance contracts.
I am attaching a self explanatory excel file, can you please help us?
Thank you,
Sophie

[Jonmo1]

The rows you highlighted yellow to indicate the ones you wanted marked, conflicts with your description.
We are currently in JUNE.
So if you want to mark the ones where the CURRENT month matches the month in B (minus 1 month)
Then the Month of JULY would be the ones highlighted.

Anyway, try this

=IF(MONTH(TODAY())=MONTH((B3&" "&C3)-1),"To Review","")

[AlKey]

Maybe this

Please Login or Register  to view this content.

[newtoexcel24]

Hi thanks for your help but it does not work.
For the month it is ok to add the minus 1 but not for the year.
The idea is to have the TO RENEW message if the formulae detects that we are one month before the expiry month of the same year. Can you please check that? it would be great

[newtoexcel24]

thanks Alkey but this does not work either. Sorry

[Jonmo1]

Did you try the formula I posted ?

[newtoexcel24]

PS no need to take into consideration the colors - that was for another personal use. Thanks ! sophie

[newtoexcel24]

Yes Jonmo1, it does not work. Here is my answer I sent to you:
Hi thanks for your help but it does not work.
For the month it is ok to add the minus 1 but not for the year.
The idea is to have the TO RENEW message if the formulae detects that we are one month before the expiry month of the same year. Can you please check that? it would be great

[Jonmo1]

I read your answer.

If you want to flag the row, where the Month in column B is the NEXT month compared to the current month.
Then it's the rows where column B = JULY that you want to highlight, because we are currently in JUNE.

But your sample data doesn't have any JULY in column B, so none of the rows were marked by the formula I posted.
If you change say B13 and B14 to July, then those rows are marked by my formula.

[newtoexcel24]

oops I think I made a mistake since there was no july in my table I couldn't see it worked so I changed it! and it works! many thanks it's great ! thank you!!!

[Jonmo1]

You're welcome.

However, after consideration, the year really should be taken into considertation.
If you had July 2015 (or any year other than current year), then it would falsely show "To Review"

Try this instead

=IF(TEXT(TODAY(),"mmmyyyy")=TEXT((B3&" "&C3)-1,"mmmyyyy"),"To Review","")