Help with Nested IFS and Dates

Hi,

I am desperately trying to get a message to appear that would reduce working out if something is in date. The date that is in column L varies according to when certificate expires. The date in L column has to be in date but some dates are in 2014 and others in 2016, these dates are usually 2 years from the date of the initial certificate date. What I am trying to do is have it so that if today's date is 1 week from the certificate being out of date then the message says "Expiring 1 Week" then as each day counts down every day in the week the message changes to say expiry 6 days, 5 days and so forth. On the expiry date and beyond I would like it to say Expired. On all other dates when it is in date I would like the message "In Date". The data is linked from 1 sheet which contains the dates but I would like the 2nd sheet just to have the messages, making it easier see what is happening with certifications.
So if L1 has the date 07-Mar-2016 and today's date was 03-Mar-2016 then message would tell me "Expiry 3 Days" but as today is 07-04-14 and L1 is 07-Mar-2016, message would be "In Date".

Thank you as I am banging my head on this one.

Dear Lizzy1965,

I have recently joined this forum. I know this post is very old and late in coming but your request is listed under "Unanswered Threads" so here is the solution. See the attached spreadsheet.

Dear Lizzy1965,

Ignore the last post and attachment. Here is the proper solution.

Hope the attachment can be seen!

Originally Posted by :) Sixthsense :)

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I hope this works.

Originally Posted by :) Sixthsense :)

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Dear Lizzy1965,

Here is the solution that I sent you. I have just added my formula to your spreadsheet.Rocky Help.xlsx

Hi,

Thank you but it still isn't doing what I would like it to do I think it may be my description that wasn't so clear. Your formula worked but when I changed the date in L3 to being 2 days ahead of today's date (what I was hoping for is that a message would appear to let me know that it would expire in 2 days but it didn't, it changed to being in date, which it is but no warning that it was nearly expired). But the message appeared if I changed the date to 1 or 2 days behind. This makes the certificate as being expired as it is less than today's date. What I am trying to do is if dates in L column are ahead of today's date they are in date. If dates are less than today's date then they are expired. In the week running up to the date of expiry I would like Expiry in.....days to appear.

Is it possible with all the dates being different expiry dates? I am getting so confused

Thank you for any advice or help you can give

Dear Lizzy1965,

Sorry about that. I had it the wrong way around. Let me know if this is correct.Rocky Help.xlsx

You are wonderful. Thank you so much. As a beginner I would never have worked this out. Very much appreciated xxxx

Dear Lizzy1965,

It's a pleasure. I enjoyed grappling with the problem. Let me know if you have anything else.