Morning All,
So I have an excel document that I want to automatically change the colour of the cell depending how close it is to the expiry date.
Dates are in the UK format.
For example
19/04/2022
When this is within 3 months of expiring I want it to go Yellow
When it is within 1 month I would like it to be Red
Anything over 3 months can be green
Thanks in advance
Administrative Note
Welcome to the forum.
Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you I have done this, it is Microsoft 365 Apps for Business
And what about when it has expired, in 6 months time?
It would help if you attached a sample Excel workbook, as you have given us no information as to which cell has the date, or indeed if there are many dates. The yellow banner at the top of the screen gives details of how to do this.
Hope this helps.
Pete
Assuming the date is in A1, CF rules as follows:
GREEN =TODAY() < EDATE(A1,-3)
AMBER =AND(TODAY() > =EDATE(A1,-3),TODAY() < EDATE(A1,-1))
RED =TODAY() > =EDATE(A1,-1)
Set the Applies To ... box to cover the entire range of dates to be formatted.
So anything greater than 3 months can appear as green
>1 month but <3 months would be yellow
<1 month would be red
See post #5.
If you are not able to apply the rules suggested, then post a workbook, as advised in post #4.
There is little point in simply repeating what you've already told us!
I believe I have added an attachment
OK - so which column of dates do you wish to apply it to and what happened when you tried my suggestion?
So this should apply to A,B,F,G,I,N-T
The second formula came up with an error. My understand of Excel if very dated so please bear with me as I am learning as I go
Thanks
When you apply the formulae, you will need to remove any blanks I inserted.
In the attached, I have applied the rules to column Q. You should be able to apply them to the other columns following my example.
NOTE OF CAUTION: I would strongly advise against this level of CF in one table - it will be very hard on the eye, and probably not as effective as you think it will be. Too much CF can be counter-productive.
AliGW on MS365 Insider (Windows) 32 bit
Security & Terrorism - 2 YEARS 14/01/2023 30/04/2021 01/02/2023 22/01/2023 12/09/2021 09/07/2021 27/09/2022 18/09/2021 12/09/2021 20/08/2021 22/05/2021 19/09/2021 19/08/2021 09/09/2023 18/09/2021 19/01/2023 16/09/2021 16/09/2021 12/04/2023 17/09/2021 11/03/2022 20/01/2023 17/09/2021 17/09/2021 21/01/2023 07/09/2023 17/09/2021 03/03/2023 17/09/2021 17/09/2021 19/09/2021 15/01/2022
Sheet: Sheet1
Last edited by AliGW; 10-19-2021 at 06:21 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Thanks you, think I have cracked this part of the task
See post #12 for instructions about signing off the thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks