I have a spreadsheet with dates running across the top and vendor invoices populating the columns based on their due date. I want to format the cells with colors based on when the invoices are due relevant to today's date. For example, anything 10 days past due or older would be red, due within 9 days would be orange, the upcoming week would be yellow and anything coming up further than a week ahead would be green.
Can you attach a sample sheet? Conditional formatting is really tricky if you don't know where to format. You're probably looking at creating a "Format all cells based on their values" and using a formula with the =TODAY() function. The trick is the "Applies To" part of the Conditional Formatting UI, that's where a sample of what you're working on will be most helpfull.
Hello and welcome to the forum.
You will have to create four Conditional Formatting rules (one rule for each desired color).
That being said, your requirements are confusing.
You said that anything within 9 days should be orange. This includes dates that are 1,2,3,4,5,6,7,8, and 9 days in the future.
You then said that anything further than a week ahead should be green. This includes dates that are 8,9,10,11,... days in the future.
What color should a date that is 8 or 9 days in the future be?
Also, what color should dates be that are between 9 and 1 days past due?
Last edited by 63falcondude; 12-01-2017 at 02:00 PM.
Hi
Try establishing 3 rules:-
Assume Invoice Number is in Column A and Due Date is in Column B
Rule1 Today()-B2>9 Format Colour Red
Rule 2 Today()-B2 <=9 Format Colour Orange
Rule 3 Today()-b2<0 Format Colour Green
With the Applies to set to the range of Cells in Column A required.
I know it is a bit vague but try and adapt to your spreadsheet.
Cheers
So this is an idea of what I have (I can't get the attachment to load)...
11/20/17 11/27/17 11/30/17 12/2/17 12/15/17
VENDOR1 100 50 75 0 0
VENDOR2 25 0 30 20 100
I would like to set it up that say anything due more than a week ago (i.e. 11/20/17) would be red, during this week (11/27 and 11/30)would be orange, this upcoming week (12/2) yellow and further in advance (12/15) would be green.
I want it to update automatically so I would use a TODAY formula. Just can't quite get it to work correctly. I know I'll need multiple conditions in order to have this work correctly.
Hi
I have attached a spreadsheet with the conditional formatting requested and a screenshot of the rules. To create the rules select conditional formatting New Rule
The rules required are:-
Rule 1 =Today()-D2>9 Format RED
Rule 2 =Today()-D2<9 Format ORANGE
Rule 3 =Today()-D2 <=0 Format YELLOW
Rule 4 =Today()<-7 Format GREEN
When Rules are created select range to apply to and select Conditional Formatting>Manage Rules Applies to Current Selection ( or you can manually change the range in the dialog box.
Hope this helps
Cheers
Hi CassKully. Welcome to the forum.So this is an idea of what I have (I can't get the attachment to load)...
It sounds like you are trying to use the paperclip icon to upload. This hasn't worked for a while.
Try this instead:
To attach a file to your post,
- be sure to desensitize the data
- click “Go Advanced” (next to Post Quick Reply – bottom right),
- scroll down until you see “Manage Attachments”, click that,
- click “Browse”.
- select your file(s)
- click “Upload”
- click “Close window”
- click “Submit Reply”
The file name will appear at the bottom of your reply.
Dave
Thank you so much! I was struggling with the absolute reference and couldn't quite get there. This fixed my issue!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks