#1
|
|||
|
|||
Conditiona Format, Icon Sets, Custom
I admit I am lost with this one.
Cell R3 - "120" (days max) Cell L5 - date "16 Dec 2022" R19 = Conditional Format Cell result ? Display: Green DOT >R3 Number Yellow DOT >= L5-10 ( 120days - 10 days for Yellow DOT warning) Grey Down Arrow when < formula ( ?? ) I would like to have the GREY down arrow show from 0 to 109 day, then the Yellow DOT from 110 to 119 days and Green DOT @120 days. 0 day to 109 days = Grey ARROW Down 110 days to 119 days = Yellow DOT 120 +5 days = Green DOT Repeat with a 240 day cycle Thanks ... |
#2
|
|||
|
|||
I hadn't seen this post before I saw and responded to the other one.
I think you're making your life complicated. I'd stick to colors and not add the shapes as a beginner. But, this is probably a good way to see how it works: https://www.youtube.com/watch?v=SIhBMvuoZNE |
#3
|
|||
|
|||
Thank you for the reply.. I have since viewed a number of youtube video's on Condition Formatting, Icon Sets etc.. Intention is to play around with this until I get it working.
Current version is now as follows... Cell M2 contains a input date "16 jan 2022" Cell R3 =M2 Cell R19 =R3+120 "15 Apr 2023" C/F: Icon Set, Custom - G,Y,R G >= | =R3+120 Y >= | =TODAY() R default I would like to see Green between 118:125 days Yellow or DOWN Arrow for anything between 1:117 It's a plan for now...any further suggestions welcome. Thanks |
#4
|
||||
|
||||
To be on the safe side please post a sample sheet with some data ( no pics please). Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Okay, so the way you described it in your second post, Kwmck, you are calling 118:125 days the length of time from the original date to today, I expect.
I mocked up a simple sheet with the cells you described. In the S column I'm showing you that the formulas are that are in the R column. I've done the date calculation below it on the sheet so you can see what I did. Cell R23 shows the difference between the original M2 date and today in days. The conditional formatting on cell R19 is based on what that number is. You can do the date difference calculating right in the conditional formatting formula, but this way when you try different dates in M2 you can see the color changes, and the number of days passed at the same time. |
#6
|
|||
|
|||
Thank you again. Working on it... FORMULATEXT is a new one for me... I will use the rest of the week to get and understanding and its use... very nice.
I will get back to you. K.. |
#7
|
||||
|
||||
@kwmck328 You could at least have acknowledged my answer..
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#8
|
|||
|
|||
Quote:
It's really the conditional formatting you could look at. I did a screen shot and pasted it in there so you could see all the steps working at once. Try changing the initial date to October 1, 2022 and January 30, 2023 and yesterday. |
#9
|
|||
|
|||
Condition Format_"Filter" Due Date.Test
Hello..A total misunderstanding on my part. New to this Forum use so forgive my tardy response. I did not realize that I had received two responces to my post from two different people. I did say thank you, but to the wrong person. So her ya go, "Thank you for the input"
I have been working on and off with this and getting closer to the end. My goal is to find a Due date that is 120 days after specific Input "DATE". In respect to the DUE Date (+120days), I want to see the Due Date DDMMYYYY with Conditional Formatting providing ICON SETS ( Green 117< to>125, Yellow 110< to>117, Red >125). Included sheet, for any suggestions for Test 1,2,and 3 conditions. k... |
#10
|
|||
|
|||
Quote:
Still work on it with what you provided. I have created a new worksheet with the noted parameters that we were talking about. Perhaps this clean sheet is best... can you give me any suggestion for the 3 test conditions. I would then play around with them to see what works best. Thanks.. K... |
#11
|
|||
|
|||
Quote:
Where I appear to have messed up is I also have overlapping conditional formats on 126 days. The red formula should have > 126, not >= 126. So if it's exactly 126 days it still falls in the green, if I understood what you wanted. All you really have to do is emulate the formula I had for the green condition, which in the case of this new file would be Code:
=AND($R$23>=118,$R$23<=126) |
#12
|
|||
|
|||
Quote:
|
#13
|
|||
|
|||
As far as I can see, yes. That formula is wider than what is displayed there.
The important factor is, does it actually work? Try plugging in different dates in the original date field (M2). Exactly 120 days ago today (6-Apr) would be 7-Dec-2022. 116 days ago would be 11-Dec, and 126 days ago 1-Dec. So 127 days ago would be 30-Nov-22. If you put in those dates you should see the various results expected. |
#14
|
|||
|
|||
Quote:
Thanks... K... |
#15
|
|||
|
|||
I haven't actually worked with those pre-set icon sets.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Custom Style Sets Stopped Working on Custom Template after Recent Microsoft Update | CynthiaKPollard | Word | 6 | 12-20-2021 08:37 PM |
RibbonX Editor - change Icon for custom AutoText Gallery | Steve2081 | Word | 15 | 04-30-2021 07:58 PM |
conditional formatting, icon sets | gohn | Excel | 3 | 06-04-2018 12:15 PM |
QuickAccess Toolbar Icon - How to make the icon display the state of the icon's action | ElfegoBaca | Word | 1 | 08-13-2016 08:42 PM |
VBA Word - Format ALL Tables - Apply Specific Formatting to Sets of Columns – Font, Border & Width | jc491 | Word VBA | 10 | 11-04-2015 04:02 PM |