• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Format a Line Across

D

dmgood

New Member
I have excel 2007. I have a spreadsheet that has dates going down the A column and numbers in all the other cells.


I need help trying to conditionally format a red line across the spreadsheet that marks where "2 weeks ago" is...that also adjusts each day automatically.


I was trying to use this formula: $A5-TODAY()-14


"A5" being where I manually put in today's date. But it is not working.


Please help!
 
Select the all the rows with data. With A5 being the active cell, CF formula is:

=$A5=TODAY()-14

format red pattern

I'm assuming you have every date listed in col A.
 
Easy.


- create a column where you set the condition so that if the date is 14 days old it is 100 (suppose the range of data is between 0 and 100).

- Any date older than 14 and less than 14 to be 0

=IF(TODAY()-A8=14,100,0)

- add this column as a secondary data

- remove the newly added legend

- remove the data points and tickers on the secondary X axis

- set the secondary data as a bar
 
For Luke M: I tried that and it didn't work...no line came up at all. When I use the formula above...it goes back 14 does and puts a red line for every day. Plus when change the date in A5 it stay with the previous red line.


Does that make any sense?


For Fred: every column already has a hidden column with calculations in it. So that wouldn't work.
 
it should work no matter. what i was trying to say was adding a column on your data sheet to house: IF(TODAY()-A8=14,100,0)


It is like adding another data series to the graph. Suppose your graphs have lines and I "assume" your X-axis is dates. All you are doing is adding a new data series, change it to a bar format, remove its legend on the graph and ticker on the Y-axis. It has nothing to do with other hidden columns, unless you have used up all the columns Excel has to offer.
 
@dmgood how about you enter =AND(WEEKDAY(A5)=7,ISEVEN(WEEKNUM(A5))) in a helper cell and adjust the 7 for what ever day of the week you consider to be the end (7 means Sat) then conditional format if the helper cell is TRUE setting a bottom border and copy down. You can change the ISEVEN for ISODD if this is the fortnight that suits your situation ie every second week is either odd or even.


I tried putting the same formula directly into CF and it didn't like it which threw me because usually if a formula returning TRUE/FALSE works in a cell it works in CF so a helper cell.
 
Hey dmgood sorry on reading your problem which I obviously didn't read properly, I can see my idea is good for definitive fortnight periods say like in a payroll situation but you want a rolling past fortnight to be highlighted.


Luke M's CF of $A5=TODAY()-14 as a CF formula looks to be what you want and worked fine for me.
 
Dear John and Fred,


I got both to work but John's worked the best for my purposes without putting in an extra column.


Thank you both so much for your help!!!!!!


Diane
 
You must log in or register to reply here.
Back
Top