Hi All,
I hope you're well .
I've been driving myself crazy trying get this formula to work and I've finally given up and have to admit defeat. I think I'm almost there though?
To start from the beginning:
I have a 'last completed' date entered in cell F2 - displays as '17-Mar-16'.
As this is due to be completed annually, I've hidden column G that states '1' in cell G2.
In cell H2, is the following formula '=IF(ISBLANK(F2),"",DATE(YEAR(F2)+G2,MONTH(F2),DAY(F2)))'. This will show a blank cell if no data in cell F2 and a year from the date if it has been entered in cell F2.
I now want to create a formula in column I that will tell me certain words if the date in cell H2 meets the following criteria:
If the date displayed in cell H2 is 1 month prior to today's date, I want the text in the cell to display as "Due in 1 Month"
If the date displayed in cell H2 is 2 months prior to today's date, I want the text in the cell to display as "Due in 2 Months"
If the date displayed in cell H2 is anything more than 2 months prior to today's date, I want the text in the cell to display as "Completed"
If the date displayed in cell H2 is equal to today's date or older then I wanted the text in the cell to display as "Expired"
I so far have the following formula:
=IF(ISBLANK(F2),"",IF(H2<(TODAY()+30),"Due in 1 Month",IF(H2<(TODAY()+60),"Due in 2 Months",IF(H2<(TODAY()+61),"Completed",IF(H2>TODAY(),"Expired")))))
I have the following examples of returned text:
F G H I
Last Completed + Years Due? Status
16-Jun-15 1 16-Jun-16 Due in 1 Month
16-Jul-15 1 16-Jul-16 Due in 1 Month
16-Aug-15 1 16-Aug-16 Due in 1 Month
16-Sep-15 1 16-Sep-16 Due in 1 Month
16-Mar-16 1 16-Mar-17 Expired
Please please can you advise and thank you in advance for any help.
Wintye
Bookmarks