Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting excel date list
Hi
i have a list of dates ie 01/02/09 03/02/09 07/02/09 what i would like to happen is when the last date exceeds the previous date by more than 5 days, highlight it in red and if it less than 5 days older highlight it green. i have tried in cell formulas and conditional formatting but so far no luck Appreciate any help thanks rslc |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting excel date list
Suppose your dates are in column A from A1 onwards. Highlight the
cells from A2 onwards (i.e. omitting the first date) as far as you think you will need, and use this formula for the first conditional format rule: =AND(A2=LOOKUP(10E10,A:A),A2A1+5) and format that as red. Use this formula for your second condition: =AND(A2=LOOKUP(10E10,A:A),A2A1,A2<=A1+5) and format that as green. OK your way out of the CF dialogue box. Hope this helps. Pete On Jan 25, 8:32*am, "rslc" wrote: Hi * * * * i have a list of dates *ie * *01/02/09 * * * * * * * * * * * * * * * * * * * * * * * * * *03/02/09 * * * * * * * * * * * * * * * * * * * * * * * * * *07/02/09 * * * * * * what i would like to happen is when the last date exceeds the previous date by more than 5 days, highlight it in red and if it less than 5 days older highlight it green. i have tried in cell formulas and conditional formatting but so far no luck *Appreciate any help * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * thanks * * * * rslc |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting excel date list
HI Pete
thanks thats just what i was hoping to do, although the colour change reverts to default ( black ) when i add another date to the column . any way to make the cells stay changed when i add another date cheers rslc "Pete_UK" wrote in message ... Suppose your dates are in column A from A1 onwards. Highlight the cells from A2 onwards (i.e. omitting the first date) as far as you think you will need, and use this formula for the first conditional format rule: =AND(A2=LOOKUP(10E10,A:A),A2A1+5) and format that as red. Use this formula for your second condition: =AND(A2=LOOKUP(10E10,A:A),A2A1,A2<=A1+5) and format that as green. OK your way out of the CF dialogue box. Hope this helps. Pete On Jan 25, 8:32 am, "rslc" wrote: Hi i have a list of dates ie 01/02/09 03/02/09 07/02/09 what i would like to happen is when the last date exceeds the previous date by more than 5 days, highlight it in red and if it less than 5 days older highlight it green. i have tried in cell formulas and conditional formatting but so far no luck Appreciate any help thanks rslc |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting excel date list
Well, you did say that you wanted the last date to be formatted
according to the previous one. Perhaps you did not highlight sufficient cells when you set up the CF to cover the addition of newer cells - in that case use the Format Painter icon to extend the CF further down the column. If you mean that you want all dates to have the appropriate format applied to them (not just the last one), then you can change the formulae to: =A2A1+5 and format that as red, and for your second condition: =AND(A2A1,A2<=A1+5) Note that these expect A2 to be the active cell in a highlighted range. Hope this helps. Pete On Jan 26, 3:35*am, "rslc" wrote: HI Pete * * * * * * thanks thats just what i was hoping to do, although the colour change reverts to default ( black ) when i add another date to the column |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting excel date list
Hi Pete
thanks thats perfect sorry i wasn't clear enough cheers rslc "Pete_UK" wrote in message ... Well, you did say that you wanted the last date to be formatted according to the previous one. Perhaps you did not highlight sufficient cells when you set up the CF to cover the addition of newer cells - in that case use the Format Painter icon to extend the CF further down the column. If you mean that you want all dates to have the appropriate format applied to them (not just the last one), then you can change the formulae to: =A2A1+5 and format that as red, and for your second condition: =AND(A2A1,A2<=A1+5) Note that these expect A2 to be the active cell in a highlighted range. Hope this helps. Pete On Jan 26, 3:35 am, "rslc" wrote: HI Pete thanks thats just what i was hoping to do, although the colour change reverts to default ( black ) when i add another date to the column . any way to make the cells stay changed when i add another date cheers rslc "Pete_UK" wrote in message ... Suppose your dates are in column A from A1 onwards. Highlight the cells from A2 onwards (i.e. omitting the first date) as far as you think you will need, and use this formula for the first conditional format rule: =AND(A2=LOOKUP(10E10,A:A),A2A1+5) and format that as red. Use this formula for your second condition: =AND(A2=LOOKUP(10E10,A:A),A2A1,A2<=A1+5) and format that as green. OK your way out of the CF dialogue box. Hope this helps. Pete On Jan 25, 8:32 am, "rslc" wrote: Hi i have a list of dates ie 01/02/09 03/02/09 07/02/09 what i would like to happen is when the last date exceeds the previous date by more than 5 days, highlight it in red and if it less than 5 days older highlight it green. i have tried in cell formulas and conditional formatting but so far no luck Appreciate any help thanks rslc- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting excel date list
You're welcome - thanks for feeding back.
Pete On Jan 27, 9:16*am, "rslc" wrote: Hi Pete * * * * * * thanks thats perfect sorry i wasn't clear enough * * * * * * * * * * * * * * * * * * * * * * * * * * * * cheers * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * rslc "Pete_UK" wrote in message ... Well, you did say that you wanted the last date to be formatted according to the previous one. Perhaps you did not highlight sufficient cells when you set up the CF to cover the addition of newer cells - in that case use the Format Painter icon to extend the CF further down the column. If you mean that you want all dates to have the appropriate format applied to them (not just the last one), then you can change the formulae to: =A2A1+5 and format that as red, and for your second condition: =AND(A2A1,A2<=A1+5) Note that these expect A2 to be the active cell in a highlighted range. Hope this helps. Pete On Jan 26, 3:35 am, "rslc" wrote: HI Pete * * * * * * thanks thats just what i was hoping to do, although the colour change reverts to default ( black ) when i add another date to the column . any way to make the cells stay changed when i add another date cheers * * * * rslc "Pete_UK" wrote in message .... Suppose your dates are in column A from A1 onwards. Highlight the cells from A2 onwards (i.e. omitting the first date) as far as you think you will need, and use this formula for the first conditional format rule: =AND(A2=LOOKUP(10E10,A:A),A2A1+5) and format that as red. Use this formula for your second condition: =AND(A2=LOOKUP(10E10,A:A),A2A1,A2<=A1+5) and format that as green. OK your way out of the CF dialogue box. Hope this helps. Pete On Jan 25, 8:32 am, "rslc" wrote: Hi * * * * i have a list of dates *ie * *01/02/09 * * * * * * * * * * * * * * * * * * * * * * * * * *03/02/09 * * * * * * * * * * * * * * * * * * * * * * * * * *07/02/09 * * * * * * what i would like to happen is when the last date exceeds the previous date by more than 5 days, highlight it in red and if it less than 5 days older highlight it green. i have tried in cell formulas and conditional formatting but so far no luck *Appreciate any help thanks * * * * rslc- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel date formatting | Excel Discussion (Misc queries) | |||
Formatting a list of data into a row in Excel | Excel Discussion (Misc queries) | |||
Excel 2003 - XML List Formatting Problem | Excel Discussion (Misc queries) | |||
Excel Drop Down List Formatting | Excel Discussion (Misc queries) | |||
Formatting the Date in Excel | Excel Discussion (Misc queries) |