Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » Follow Up on Formula Question asked last week | Excel forum

# Follow Up on Formula Question asked last week | Excel forum

Resolved · Medium Priority · Version 365

Edited on Thu 27 Oct 2022, 19:14

Kay has attended:
Excel Advanced - Formulas & Functions course

Hi Marius,

My apologies to reach out to you again.

Last week, you gave me a solution to my question below.

I would like to create the following formula and also add in conditioning format

If due date is less than 6 months then return 3-Low
If the due date is greater than or equal to 6 months then return 2- Medium
If due date is greater than 1 year then return 1- High

Solution:
=IF(B2>=EDATE(TODAY(),12),1,IF(B2>=EDATE(TODAY(),6),2,3))

This formula assumes that column B holds the dates. The EDATE function looks at dates exactly x months away from a given date (in this case TODAY().

This formula will always assign 1 for >= 1 year, 2 for >= 6 months up to 1 year, and 3 for less than 6 months.

Afterwards, you could apply conditional formatting based on 1, 2 or 3.

Having tested this on my file, it does not appear to be working.

e.g. I have a date as 08 Jul 20 (Date format is DD/MMM/YY) which I assume would return 1 however this is not the case. It only returns the number 3 all the way down the columns.

Also, I cant seem to apply conditional formatting based on 1, 2 or 3 hence appreciate any pointers on this area too.

Appreciate if you can kindly check and confirm.

Many Thanks
Kay

Hi Kay,

No problem at all! Excel has many moving parts which can prevent it from doing the correct thing.

Would it be possible to send us a sample Excel file with the data that is not working for you?

I could have a look and possibly spot some issues.

info@stl-training.co.uk

Many thanks
Marius

Hi Marius,

I have sent my file to you via email to the address below;

info@stl-training.co.uk

Please let me know how best to resolve this.

Hi Kay,

Thanks for sending the Excel file. Just to be sure I understand what needs to happen: I assumed you were working with future dates? If so, the formula works. The reason you're getting 3s is because all the dates in your data are in the past, which I didn't assume we would have.

If you're referencing past dates, the formula would look different. Please let me know if this needs to be the case.

Kind regards
Marius

Hi Marius,

My apologies I wasn't clear in my instructions.

You are correct, all the dates I am working with are past dates.

Is it possible for you to assist me with a different formula to work for past dates as highlighted in my file?

Many thanks

Hi Kay,

Apologies for the delay, it's been a busy few days at STL. Thank you for clarifying the context of your dates. The following formula worked for me on the dates in your spreadsheet:

=IF(A2<=EDATE(TODAY(),-12),1,IF(A2<=EDATE(TODAY(),-6),2,3))

It looks at past dates.

To apply conditional formatting afterwards, you need to create three separate rules on your priority cells:

Home > Conditional Formatting > Highlight Cells Rules > Equal To

In the dialogue box, type either 1, 2 or 3 for each rule you create. Set a colour for each rule.

I hope this helps.

Kind regards
Marius Barnard
STL

Thu 10 Nov 2022: Automatically marked as resolved.

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.