Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If cell = now current Qtr 28 days then RED
Im trying to work out a formula which is based upon quarters. So, if the
date in column C is more than 28 days + 3 months (i.e. last quarter) + this quarter then the cell displays RED The quarters run Qtr 1 01/01 31/03 Qtr 2 01/04 30/06 Qtr 3 01/07 31/09 Qtr 4 01/10 31/12 So essentially, working on the quarter were in (Currently Qtr 3) if a date is displayed that is older than 28 days prior to the beginning of Qtr 2 I want it highlighting. But I need it to be consistent so I can repeat it at any time in the year. Any help would be very much appreciated. Regards, Terry |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If cell = now current Qtr 28 days then RED
1. Select Column C
2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(C1<"",C1<DATE(YEAR(TODAY()),CEILING(MONTH(TO DAY()),3)-5,1)-28) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "Tel" wrote: Im trying to work out a formula which is based upon quarters. So, if the date in column C is more than 28 days + 3 months (i.e. last quarter) + this quarter then the cell displays RED The quarters run Qtr 1 01/01 31/03 Qtr 2 01/04 30/06 Qtr 3 01/07 31/09 Qtr 4 01/10 31/12 So essentially, working on the quarter were in (Currently Qtr 3) if a date is displayed that is older than 28 days prior to the beginning of Qtr 2 I want it highlighting. But I need it to be consistent so I can repeat it at any time in the year. Any help would be very much appreciated. Regards, Terry |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If cell = now current Qtr 28 days then RED
Jacob,
Not only does it work I happen to think you're an absolute bloody star. I could kiss youi, but if it's all the same I won't :-) Thank you so much Terry "Jacob Skaria" wrote: 1. Select Column C 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(C1<"",C1<DATE(YEAR(TODAY()),CEILING(MONTH(TO DAY()),3)-5,1)-28) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "Tel" wrote: Im trying to work out a formula which is based upon quarters. So, if the date in column C is more than 28 days + 3 months (i.e. last quarter) + this quarter then the cell displays RED The quarters run Qtr 1 01/01 31/03 Qtr 2 01/04 30/06 Qtr 3 01/07 31/09 Qtr 4 01/10 31/12 So essentially, working on the quarter were in (Currently Qtr 3) if a date is displayed that is older than 28 days prior to the beginning of Qtr 2 I want it highlighting. But I need it to be consistent so I can repeat it at any time in the year. Any help would be very much appreciated. Regards, Terry |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If cell = now current Qtr 28 days then RED
Terry, thanks for your feedback..Cheers
If this post helps click Yes --------------- Jacob Skaria "Tel" wrote: Jacob, Not only does it work I happen to think you're an absolute bloody star. I could kiss youi, but if it's all the same I won't :-) Thank you so much Terry "Jacob Skaria" wrote: 1. Select Column C 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =AND(C1<"",C1<DATE(YEAR(TODAY()),CEILING(MONTH(TO DAY()),3)-5,1)-28) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "Tel" wrote: Im trying to work out a formula which is based upon quarters. So, if the date in column C is more than 28 days + 3 months (i.e. last quarter) + this quarter then the cell displays RED The quarters run Qtr 1 01/01 31/03 Qtr 2 01/04 30/06 Qtr 3 01/07 31/09 Qtr 4 01/10 31/12 So essentially, working on the quarter were in (Currently Qtr 3) if a date is displayed that is older than 28 days prior to the beginning of Qtr 2 I want it highlighting. But I need it to be consistent so I can repeat it at any time in the year. Any help would be very much appreciated. Regards, Terry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Days Left in Current Year | Excel Worksheet Functions | |||
finding 75 days before current date | Excel Worksheet Functions | |||
days since 1/1/current year | Excel Discussion (Misc queries) | |||
get a rolling sum between current date - 30 days | Excel Worksheet Functions | |||
Having a due date flag in a different colour 30 days from current. | Excel Worksheet Functions |