Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Anniversary of a date is due - conditional formatting
Hi
I have been scratching my head to come up with a way of doing the following. Imagine there is a date entered in cell J3 I would like the cell to be shaded green up untill one month before the anniversary of the date when I would like it to change to amber. Once the date in cell J£ is over a year old I would like it to change to red. Any ideas?? Thanks Carl |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Anniversary of a date is due - conditional formatting
Use the "formula" option;
if date() (A1+365) 'then select red if date() (A1-31) 'then select amber if date() <= (A1-30) 'then select green Alternatively, skip condition three and just format the cell colors to green. If either condition 1 or 2 are met, they should supersede the background color. If 31 days is not precise enough (if you need exactly one month, regardless of leap years, etc) then reply to the group for assistance looking at the month() and year() formulas to get an exact month prior. HTH Keith "Carl Mountney" wrote: Hi I have been scratching my head to come up with a way of doing the following. Imagine there is a date entered in cell J3 I would like the cell to be shaded green up untill one month before the anniversary of the date when I would like it to change to amber. Once the date in cell J£ is over a year old I would like it to change to red. Any ideas?? Thanks Carl |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Anniversary of a date is due - conditional formatting
I too need the red function based on date. I have cells with dates (G2, G10,
etc with dates like 22-Jul-07) and need that cell to go red when the date hits 365 days from the day displayed. I tried using "ker_01"s advise but don't get any result at all with the "if date...". If I change if to "=" I get result "FALSE" Perhaps "ker_01" could provide more exact info? Please? -- Dean Johnson "ker_01" wrote: Use the "formula" option; if date() (A1+365) 'then select red if date() (A1-31) 'then select amber if date() <= (A1-30) 'then select green Alternatively, skip condition three and just format the cell colors to green. If either condition 1 or 2 are met, they should supersede the background color. If 31 days is not precise enough (if you need exactly one month, regardless of leap years, etc) then reply to the group for assistance looking at the month() and year() formulas to get an exact month prior. HTH Keith "Carl Mountney" wrote: Hi I have been scratching my head to come up with a way of doing the following. Imagine there is a date entered in cell J3 I would like the cell to be shaded green up untill one month before the anniversary of the date when I would like it to change to amber. Once the date in cell J£ is over a year old I would like it to change to red. Any ideas?? Thanks Carl |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Anniversary of a date is due - conditional formatting
Dean,
It didn't work because DATE requires year, month, and day to be passed to it. Try using one of these two formulas - not sure what specifically you mean by "within one year" To highlight dates that occured exactly one year ago today =TODAY() = (A1+365) To highlight dates within a range (you are within +/- 5 days of the anniversary) =AND(TODAY() = (A1+360), TODAY() <= (A1+370)) HTH, Bernie MS Excel MVP "Dean Johnson" wrote in message ... I too need the red function based on date. I have cells with dates (G2, G10, etc with dates like 22-Jul-07) and need that cell to go red when the date hits 365 days from the day displayed. I tried using "ker_01"s advise but don't get any result at all with the "if date...". If I change if to "=" I get result "FALSE" Perhaps "ker_01" could provide more exact info? Please? -- Dean Johnson "ker_01" wrote: Use the "formula" option; if date() (A1+365) 'then select red if date() (A1-31) 'then select amber if date() <= (A1-30) 'then select green Alternatively, skip condition three and just format the cell colors to green. If either condition 1 or 2 are met, they should supersede the background color. If 31 days is not precise enough (if you need exactly one month, regardless of leap years, etc) then reply to the group for assistance looking at the month() and year() formulas to get an exact month prior. HTH Keith "Carl Mountney" wrote: Hi I have been scratching my head to come up with a way of doing the following. Imagine there is a date entered in cell J3 I would like the cell to be shaded green up untill one month before the anniversary of the date when I would like it to change to amber. Once the date in cell J£ is over a year old I would like it to change to red. Any ideas?? Thanks Carl |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Anniversary of a date is due - conditional formatting
Thank you Bernie!
Now, how do apply that to the spreadsheet? The range of cells D2 through J18 all have date cells. Sorry to be so dense. -- Dean Johnson "Bernie Deitrick" wrote: Dean, It didn't work because DATE requires year, month, and day to be passed to it. Try using one of these two formulas - not sure what specifically you mean by "within one year" To highlight dates that occured exactly one year ago today =TODAY() = (A1+365) To highlight dates within a range (you are within +/- 5 days of the anniversary) =AND(TODAY() = (A1+360), TODAY() <= (A1+370)) HTH, Bernie MS Excel MVP "Dean Johnson" wrote in message ... I too need the red function based on date. I have cells with dates (G2, G10, etc with dates like 22-Jul-07) and need that cell to go red when the date hits 365 days from the day displayed. I tried using "ker_01"s advise but don't get any result at all with the "if date...". If I change if to "=" I get result "FALSE" Perhaps "ker_01" could provide more exact info? Please? -- Dean Johnson "ker_01" wrote: Use the "formula" option; if date() (A1+365) 'then select red if date() (A1-31) 'then select amber if date() <= (A1-30) 'then select green Alternatively, skip condition three and just format the cell colors to green. If either condition 1 or 2 are met, they should supersede the background color. If 31 days is not precise enough (if you need exactly one month, regardless of leap years, etc) then reply to the group for assistance looking at the month() and year() formulas to get an exact month prior. HTH Keith "Carl Mountney" wrote: Hi I have been scratching my head to come up with a way of doing the following. Imagine there is a date entered in cell J3 I would like the cell to be shaded green up untill one month before the anniversary of the date when I would like it to change to amber. Once the date in cell J£ is over a year old I would like it to change to red. Any ideas?? Thanks Carl |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Anniversary of a date is due - conditional formatting
Dean,
Select cells D2:J18, with cell D2 being the activecell. Then use (XL2003 or earlier) Format / Conditional Formatting... in the dropdown choose "Formula is" In 2007, use the CF button, then choose "Highlight Cell Rules" "More Rules" "Use a formula"..... In either case, use the formula with D2 in the place of A1: =TODAY() = (D2+365) or =AND(TODAY() = (D2+360), TODAY() <= (D2+370)) Just like regular formulas, Excel will update the CF formula for each cell, so the D2s become D3s......J18s HTH, Bernie MS Excel MVP "Dean Johnson" wrote in message ... Thank you Bernie! Now, how do apply that to the spreadsheet? The range of cells D2 through J18 all have date cells. Sorry to be so dense. -- Dean Johnson "Bernie Deitrick" wrote: Dean, It didn't work because DATE requires year, month, and day to be passed to it. Try using one of these two formulas - not sure what specifically you mean by "within one year" To highlight dates that occured exactly one year ago today =TODAY() = (A1+365) To highlight dates within a range (you are within +/- 5 days of the anniversary) =AND(TODAY() = (A1+360), TODAY() <= (A1+370)) HTH, Bernie MS Excel MVP "Dean Johnson" wrote in message ... I too need the red function based on date. I have cells with dates (G2, G10, etc with dates like 22-Jul-07) and need that cell to go red when the date hits 365 days from the day displayed. I tried using "ker_01"s advise but don't get any result at all with the "if date...". If I change if to "=" I get result "FALSE" Perhaps "ker_01" could provide more exact info? Please? -- Dean Johnson "ker_01" wrote: Use the "formula" option; if date() (A1+365) 'then select red if date() (A1-31) 'then select amber if date() <= (A1-30) 'then select green Alternatively, skip condition three and just format the cell colors to green. If either condition 1 or 2 are met, they should supersede the background color. If 31 days is not precise enough (if you need exactly one month, regardless of leap years, etc) then reply to the group for assistance looking at the month() and year() formulas to get an exact month prior. HTH Keith "Carl Mountney" wrote: Hi I have been scratching my head to come up with a way of doing the following. Imagine there is a date entered in cell J3 I would like the cell to be shaded green up untill one month before the anniversary of the date when I would like it to change to amber. Once the date in cell J£ is over a year old I would like it to change to red. Any ideas?? Thanks Carl |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Anniversary of a date is due - conditional formatting
Thanks again Bernie - it's great that you're so fast.
Did as described, but nothing changed. It's Office 2007 if that helps. Any other ideas? Dean -- Dean Johnson "Bernie Deitrick" wrote: Dean, Select cells D2:J18, with cell D2 being the activecell. Then use (XL2003 or earlier) Format / Conditional Formatting... in the dropdown choose "Formula is" In 2007, use the CF button, then choose "Highlight Cell Rules" "More Rules" "Use a formula"..... In either case, use the formula with D2 in the place of A1: =TODAY() = (D2+365) or =AND(TODAY() = (D2+360), TODAY() <= (D2+370)) Just like regular formulas, Excel will update the CF formula for each cell, so the D2s become D3s......J18s HTH, Bernie MS Excel MVP "Dean Johnson" wrote in message ... Thank you Bernie! Now, how do apply that to the spreadsheet? The range of cells D2 through J18 all have date cells. Sorry to be so dense. -- Dean Johnson "Bernie Deitrick" wrote: Dean, It didn't work because DATE requires year, month, and day to be passed to it. Try using one of these two formulas - not sure what specifically you mean by "within one year" To highlight dates that occured exactly one year ago today =TODAY() = (A1+365) To highlight dates within a range (you are within +/- 5 days of the anniversary) =AND(TODAY() = (A1+360), TODAY() <= (A1+370)) HTH, Bernie MS Excel MVP "Dean Johnson" wrote in message ... I too need the red function based on date. I have cells with dates (G2, G10, etc with dates like 22-Jul-07) and need that cell to go red when the date hits 365 days from the day displayed. I tried using "ker_01"s advise but don't get any result at all with the "if date...". If I change if to "=" I get result "FALSE" Perhaps "ker_01" could provide more exact info? Please? -- Dean Johnson "ker_01" wrote: Use the "formula" option; if date() (A1+365) 'then select red if date() (A1-31) 'then select amber if date() <= (A1-30) 'then select green Alternatively, skip condition three and just format the cell colors to green. If either condition 1 or 2 are met, they should supersede the background color. If 31 days is not precise enough (if you need exactly one month, regardless of leap years, etc) then reply to the group for assistance looking at the month() and year() formulas to get an exact month prior. HTH Keith "Carl Mountney" wrote: Hi I have been scratching my head to come up with a way of doing the following. Imagine there is a date entered in cell J3 I would like the cell to be shaded green up untill one month before the anniversary of the date when I would like it to change to amber. Once the date in cell J£ is over a year old I would like it to change to red. Any ideas?? Thanks Carl |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Anniversary of a date is due - conditional formatting
Dean,
If thevalues are not true dates, but are strings that look like dates, then try =TODAY() = (DATEVALUE(D2)+365) etc... PS. you also need to set the fill color in the CF dialog.... some folks miss that part. HTH, Bernie MS Excel MVP "Dean Johnson" wrote in message ... Thanks again Bernie - it's great that you're so fast. Did as described, but nothing changed. It's Office 2007 if that helps. Any other ideas? Dean -- Dean Johnson "Bernie Deitrick" wrote: Dean, Select cells D2:J18, with cell D2 being the activecell. Then use (XL2003 or earlier) Format / Conditional Formatting... in the dropdown choose "Formula is" In 2007, use the CF button, then choose "Highlight Cell Rules" "More Rules" "Use a formula"..... In either case, use the formula with D2 in the place of A1: =TODAY() = (D2+365) or =AND(TODAY() = (D2+360), TODAY() <= (D2+370)) Just like regular formulas, Excel will update the CF formula for each cell, so the D2s become D3s......J18s HTH, Bernie MS Excel MVP "Dean Johnson" wrote in message ... Thank you Bernie! Now, how do apply that to the spreadsheet? The range of cells D2 through J18 all have date cells. Sorry to be so dense. -- Dean Johnson "Bernie Deitrick" wrote: Dean, It didn't work because DATE requires year, month, and day to be passed to it. Try using one of these two formulas - not sure what specifically you mean by "within one year" To highlight dates that occured exactly one year ago today =TODAY() = (A1+365) To highlight dates within a range (you are within +/- 5 days of the anniversary) =AND(TODAY() = (A1+360), TODAY() <= (A1+370)) HTH, Bernie MS Excel MVP "Dean Johnson" wrote in message ... I too need the red function based on date. I have cells with dates (G2, G10, etc with dates like 22-Jul-07) and need that cell to go red when the date hits 365 days from the day displayed. I tried using "ker_01"s advise but don't get any result at all with the "if date...". If I change if to "=" I get result "FALSE" Perhaps "ker_01" could provide more exact info? Please? -- Dean Johnson "ker_01" wrote: Use the "formula" option; if date() (A1+365) 'then select red if date() (A1-31) 'then select amber if date() <= (A1-30) 'then select green Alternatively, skip condition three and just format the cell colors to green. If either condition 1 or 2 are met, they should supersede the background color. If 31 days is not precise enough (if you need exactly one month, regardless of leap years, etc) then reply to the group for assistance looking at the month() and year() formulas to get an exact month prior. HTH Keith "Carl Mountney" wrote: Hi I have been scratching my head to come up with a way of doing the following. Imagine there is a date entered in cell J3 I would like the cell to be shaded green up untill one month before the anniversary of the date when I would like it to change to amber. Once the date in cell J£ is over a year old I would like it to change to red. Any ideas?? Thanks Carl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Conditional Formatting on Anniversary Date | Excel Discussion (Misc queries) | |||
Conditional Result based on Anniversary Date | Excel Worksheet Functions | |||
Conditional Formatting Function for Anniversary Dates | Excel Worksheet Functions | |||
Anniversary of a date | Excel Discussion (Misc queries) | |||
How do I get an anniversary date? | Excel Worksheet Functions |