Help with MONTH / YEAR functions

[Whotton]

Hi guys,

Quite an extensive query here, I am putting together a tracking sheet for contracts my department holds with it's clients, these contracts under go a 2 yearly periodic review and we actively need to chase the clients to initiate the review process in order to display due diligence ect. The below table is setup to calculate dates of when we send off our first reminder (month 22) and second reminder (month 23) based on the contracts last update, what I am ultimately looking to do is populate the status field accordingly based on where we are within the update cycle.

Currently I have the below formula setup which pulls from certain fields within the table and populates this column, I am using the output to then trigger conditional formatting which highlights the entire row and also within a pivot table elsewhere.

=IF([@[Programme status]]="In Setup Phase","In Setup Phase",IF([@[Last update within last calendar month]]="Awaiting Date Of Last Update","Awaiting Date Of Last Update",IF([@[Last update within last calendar month]]=TRUE,"Recently Updated",IF(OR(AND([@[Update First Reminder due date]]<TODAY(),[@[1st Reminder Sent Date ]]=""),AND([@[Update Second Reminder Due Date]]<TODAY(),[@[2nd Reminder Sent Date]]=""),AND([@[Update Final Due Date]]<TODAY(),[@[Final Reminder Sent Date]]="")),"Overdue",IF(AND([@[1st Reminder Sent Date ]]<>"",[@[Update Second Reminder Due Date]]>TODAY()),"First Reminder Sent",IF([@[Update First Reminder due date]]>TODAY(),"Not Due Yet","Update Due"))))))

I am hoping to add in an couple of additional nested IF statements which will allow me to determine if due dates occur next/this calendar month and to bypass the statement should the respective sent date field be populated. So far I have come up with the following which is just looking at the first reminder...

IF(AND(MONTH([@[Update First Reminder due date]])=MONTH(TODAY())),YEAR(([@[Update First Reminder due date]])=YEAR(TODAY())),[@[1st Reminder Sent Date ]]=""),"First Reminder Due This Month",IF(AND(MONTH([@[Update First Reminder due date]])=MONTH(TODAY+1()),YEAR([@[Update First Reminder due date]])=YEAR(TODAY()),[@[1st Reminder Sent Date ]]=""),"First Reminder Due Next Month",

However I seem to be getting an error within this statement? I think it may be due to the serial used... can anyone point me in the right direction?
I've never used MONTH/DATE functions previously so if there is a better way of doing this please let me know!

Thanks in advance

 

[cyrilbrd]

TODAY+1
Is TODAY a name range?
If TODAY is a formula then consider TODAY()+1 instead of TODAY+1()

 

[Whotton]

Thanks for coming back to me!

I checked and you were right that's where I had gone wrong, changing it to TODAY()+1 fixed the formula.

 

[cyrilbrd]

Most welcome, thanks for the feedback.