Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-30-2023, 11:03 AM
Kwmck328 Kwmck328 is offline Conditiona Format, Icon Sets, Custom Mac OS X Conditiona Format, Icon Sets, Custom Office for Mac 2011
Novice
Conditiona Format, Icon Sets, Custom
 
Join Date: Feb 2015
Posts: 23
Kwmck328 is on a distinguished road
Default 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 ...
Reply With Quote
  #2  
Old 03-31-2023, 01:59 PM
kilroyscarnival kilroyscarnival is offline Conditiona Format, Icon Sets, Custom Windows 10 Conditiona Format, Icon Sets, Custom Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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
Reply With Quote
  #3  
Old 04-01-2023, 07:54 AM
Kwmck328 Kwmck328 is offline Conditiona Format, Icon Sets, Custom Mac OS X Conditiona Format, Icon Sets, Custom Office for Mac 2011
Novice
Conditiona Format, Icon Sets, Custom
 
Join Date: Feb 2015
Posts: 23
Kwmck328 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 04-01-2023, 11:52 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditiona Format, Icon Sets, Custom Windows 10 Conditiona Format, Icon Sets, Custom Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #5  
Old 04-03-2023, 07:29 AM
kilroyscarnival kilroyscarnival is offline Conditiona Format, Icon Sets, Custom Windows 10 Conditiona Format, Icon Sets, Custom Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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.
Attached Files
File Type: xlsx Conditional Formatting.xlsx (68.6 KB, 3 views)
Reply With Quote
  #6  
Old 04-03-2023, 07:42 PM
Kwmck328 Kwmck328 is offline Conditiona Format, Icon Sets, Custom Mac OS X Conditiona Format, Icon Sets, Custom Office for Mac 2011
Novice
Conditiona Format, Icon Sets, Custom
 
Join Date: Feb 2015
Posts: 23
Kwmck328 is on a distinguished road
Default

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..
Reply With Quote
  #7  
Old 04-04-2023, 01:05 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditiona Format, Icon Sets, Custom Windows 10 Conditiona Format, Icon Sets, Custom Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

@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
Reply With Quote
  #8  
Old 04-04-2023, 05:17 AM
kilroyscarnival kilroyscarnival is offline Conditiona Format, Icon Sets, Custom Windows 10 Conditiona Format, Icon Sets, Custom Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Quote:
Originally Posted by Kwmck328 View Post
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..
Oh, I only used FORMULATEXT in order for it to display the formula in the cell next to it. It's handy for that.

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.
Reply With Quote
  #9  
Old 04-04-2023, 08:47 PM
Kwmck328 Kwmck328 is offline Conditiona Format, Icon Sets, Custom Mac OS X Conditiona Format, Icon Sets, Custom Office for Mac 2011
Novice
Conditiona Format, Icon Sets, Custom
 
Join Date: Feb 2015
Posts: 23
Kwmck328 is on a distinguished road
Default Condition Format_"Filter" Due Date.Test

Quote:
Originally Posted by Pecoflyer View Post
@kwmck328 You could at least have acknowledged my answer..
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...
Attached Files
File Type: xlsx Excel_Conditional Format_Filter Due Warning_1,2,3.xlsx (11.8 KB, 0 views)
Reply With Quote
  #10  
Old 04-04-2023, 08:56 PM
Kwmck328 Kwmck328 is offline Conditiona Format, Icon Sets, Custom Mac OS X Conditiona Format, Icon Sets, Custom Office for Mac 2011
Novice
Conditiona Format, Icon Sets, Custom
 
Join Date: Feb 2015
Posts: 23
Kwmck328 is on a distinguished road
Default

Quote:
Originally Posted by kilroyscarnival View Post
Oh, I only used FORMULATEXT in order for it to display the formula in the cell next to it. It's handy for that.

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.
Hi.... I picked that up about the FORMULATEXT, in that it was a nice add on but not typically required.
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...
Attached Files
File Type: xlsx Excel_Conditional Format_Filter Due Warning_1,2,3.xlsx (11.8 KB, 1 views)
Reply With Quote
  #11  
Old 04-05-2023, 11:14 AM
kilroyscarnival kilroyscarnival is offline Conditiona Format, Icon Sets, Custom Windows 10 Conditiona Format, Icon Sets, Custom Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Quote:
Originally Posted by Kwmck328 View Post
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...
Hi, it looks like you have the conditional formatting for the yellow (less than or equal to 118 days' difference between original date and today) and the red (greater than 126 days' difference between those dates.) Note that I used greater than or equal to 117 days, not 118, because I presumed your third category would cover 118 days in the green condition.

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)
Reply With Quote
  #12  
Old 04-05-2023, 11:56 AM
Kwmck328 Kwmck328 is offline Conditiona Format, Icon Sets, Custom Mac OS X Conditiona Format, Icon Sets, Custom Office for Mac 2011
Novice
Conditiona Format, Icon Sets, Custom
 
Join Date: Feb 2015
Posts: 23
Kwmck328 is on a distinguished road
Default

Quote:
Originally Posted by kilroyscarnival View Post
Hi, it looks like you have the conditional formatting for the yellow (less than or equal to 118 days' difference between original date and today) and the red (greater than 126 days' difference between those dates.) Note that I used greater than or equal to 117 days, not 118, because I presumed your third category would cover 118 days in the green condition.

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)
Thank you for the assistance. Does this represent what you mentioned above for Test #1 of this new workbook sheet? attached....
Attached Images
File Type: png Screen Shot 2023-04-05 at 2.49.10 PM.png (58.9 KB, 12 views)
Reply With Quote
  #13  
Old 04-06-2023, 05:47 AM
kilroyscarnival kilroyscarnival is offline Conditiona Format, Icon Sets, Custom Windows 10 Conditiona Format, Icon Sets, Custom Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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.
Reply With Quote
  #14  
Old 04-06-2023, 06:45 AM
Kwmck328 Kwmck328 is offline Conditiona Format, Icon Sets, Custom Mac OS X Conditiona Format, Icon Sets, Custom Office for Mac 2011
Novice
Conditiona Format, Icon Sets, Custom
 
Join Date: Feb 2015
Posts: 23
Kwmck328 is on a distinguished road
Red face

Quote:
Originally Posted by kilroyscarnival View Post
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.
Thank you so much. I input various dates as you suggested, and now have both Test 1 and Test 2 scenarios working, even with changing the basic rule... I'm learning... Now another "Q" regarding Test #3, ICON SETS "Street Lights" in cell V23. How could I apply the same rules to this version (Test#3)?
Thanks... K...
Attached Images
File Type: png Screen Shot 2023-04-06 at 9.35.36 AM.png (43.3 KB, 9 views)
Reply With Quote
  #15  
Old 04-06-2023, 08:14 AM
kilroyscarnival kilroyscarnival is offline Conditiona Format, Icon Sets, Custom Windows 10 Conditiona Format, Icon Sets, Custom Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

I haven't actually worked with those pre-set icon sets.
Reply With Quote
Reply

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
Conditiona Format, Icon Sets, Custom 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
Conditiona Format, Icon Sets, Custom 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft