| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date help please
Office 2003
Win XP Pro. I have a spreadsheet with A)col. "names" and B)col. "dates paid" My desire is to use another column (say C), with function to highlight "any date after a certain date has passed". I have tried conditional formatting but cannot get it right!! Col A Col.B Col. C smith 15/2/08 if after 18/2/08 then "bad", otherwise col. B TIA Terry |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
Might be better to use a cell for your reference date, eg D1, rather
than hard-code it within the formula. So, put 18/2/08 in D1 and this formula in C1: =IF(B1D$1,"bad",B1) Format the cell as a date, and then copy this down to cover the data you have in column B. Hope this helps. Pete On Feb 11, 4:30*pm, "Terry" wrote: Office 2003 Win XP Pro. I have a spreadsheet with A)col. "names" * * and * * * * B)col.. "dates paid" My desire is to use another column (say C), with function to highlight "any date after a certain date has passed". I have tried conditional formatting but cannot get it right!! Col A * * * * * Col.B * * * * * * * * * * * * * * Col. C *smith * * * * *15/2/08 * * * * * * *if after 18/2/08 then "bad", *otherwise col. B TIA Terry |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
I carried on searching for solution and came up with this one, which I can
modify: =IF(K3 $R$3, "LATE",IF(K3 < $R$3,"")) Cells are adjusted to suit my new spreadsheet. I do apologise if any inconvenience guys. Terry "Terry" wrote in message ... Office 2003 Win XP Pro. I have a spreadsheet with A)col. "names" and B)col. "dates paid" My desire is to use another column (say C), with function to highlight "any date after a certain date has passed". I have tried conditional formatting but cannot get it right!! Col A Col.B Col. C smith 15/2/08 if after 18/2/08 then "bad", otherwise col. B TIA Terry |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
Just check what happens when K3 is the same date as R3.
Pete On Feb 11, 5:17*pm, "Terry" wrote: I carried on searching for solution and came up with this one, which I can modify: =IF(K3 $R$3, "LATE",IF(K3 < $R$3,"")) Cells are adjusted to suit my new spreadsheet. I do apologise if any inconvenience guys. Terry"Terry" wrote in message ... Office 2003 Win XP Pro. I have a spreadsheet with A)col. "names" * * and * * * * B)col. "dates paid" My desire is to use another column (say C), with function to highlight "any date after a certain date has passed". I have tried conditional formatting but cannot get it right!! Col A * * * * * Col.B * * * * * * * * * * * * * * Col. C smith * * * * *15/2/08 * * * * * * *if after 18/2/08 then "bad", otherwise col. B TIA Terry- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
Peter ...thank you
Terry "Pete_UK" wrote in message ... Might be better to use a cell for your reference date, eg D1, rather than hard-code it within the formula. So, put 18/2/08 in D1 and this formula in C1: =IF(B1D$1,"bad",B1) Format the cell as a date, and then copy this down to cover the data you have in column B. Hope this helps. Pete On Feb 11, 4:30 pm, "Terry" wrote: Office 2003 Win XP Pro. I have a spreadsheet with A)col. "names" and B)col. "dates paid" My desire is to use another column (say C), with function to highlight "any date after a certain date has passed". I have tried conditional formatting but cannot get it right!! Col A Col.B Col. C smith 15/2/08 if after 18/2/08 then "bad", otherwise col. B TIA Terry |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
OK Peter
Amended formula to include = along with <, , to rid the "false" kicking in. Terry "Pete_UK" wrote in message ... Just check what happens when K3 is the same date as R3. Pete On Feb 11, 5:17 pm, "Terry" wrote: I carried on searching for solution and came up with this one, which I can modify: =IF(K3 $R$3, "LATE",IF(K3 < $R$3,"")) Cells are adjusted to suit my new spreadsheet. I do apologise if any inconvenience guys. Terry"Terry" wrote in message ... Office 2003 Win XP Pro. I have a spreadsheet with A)col. "names" and B)col. "dates paid" My desire is to use another column (say C), with function to highlight "any date after a certain date has passed". I have tried conditional formatting but cannot get it right!! Col A Col.B Col. C smith 15/2/08 if after 18/2/08 then "bad", otherwise col. B TIA Terry- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
You're welcome, Terry - glad to help.
Pete On Feb 11, 5:58*pm, "Terry" wrote: Peter ...thank you Terry"Pete_UK" wrote in message ... Might be better to use a cell for your reference date, eg D1, rather than hard-code it within the formula. So, put 18/2/08 in D1 and this formula in C1: =IF(B1D$1,"bad",B1) Format the cell as a date, and then copy this down to cover the data you have in column B. Hope this helps. Pete On Feb 11, 4:30 pm, "Terry" wrote: Office 2003 Win XP Pro. I have a spreadsheet with A)col. "names" and B)col. "dates paid" My desire is to use another column (say C), with function to highlight "any date after a certain date has passed". I have tried conditional formatting but cannot get it right!! Col A Col.B Col. C smith 15/2/08 if after 18/2/08 then "bad", otherwise col. B TIA Terry- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
In which case you can amend it further to:
=IF(K3 $R$3, "LATE","") to get the same effect more efficiently. Hope this helps. Pete On Feb 11, 6:01*pm, "Terry" wrote: OK Peter Amended formula to include = along with <, , to rid the "false" kicking in. Terry |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
If by that last reply you mean that you've changed your formula from
=IF(K3 $R$3, "LATE",IF(K3 < $R$3,"")) to =IF(K3 $R$3, "LATE",IF(K3 <= $R$3,"")) then you might want to have another think about how you would get to the second test in any situation where it would not be satisfied, remembering that you've already done the first test. In other words, why not just =IF(K3 $R$3, "LATE", "") ? If I take your reply literally and you've actually changed the formula to =IF(K3 = $R$3, "LATE",IF(K3 <= $R$3,"")) then tghere's even more confusion, in that if you've accepted the = case in the first test, you can't get to the second test with =. If = counts as late, then all you need is =IF(K3 = $R$3, "LATE", "") -- David Biddulph "Terry" wrote in message ... OK Peter Amended formula to include = along with <, , to rid the "false" kicking in. Terry "Pete_UK" wrote in message ... Just check what happens when K3 is the same date as R3. Pete On Feb 11, 5:17 pm, "Terry" wrote: I carried on searching for solution and came up with this one, which I can modify: =IF(K3 $R$3, "LATE",IF(K3 < $R$3,"")) Cells are adjusted to suit my new spreadsheet. I do apologise if any inconvenience guys. Terry"Terry" wrote in message ... Office 2003 Win XP Pro. I have a spreadsheet with A)col. "names" and B)col. "dates paid" My desire is to use another column (say C), with function to highlight "any date after a certain date has passed". I have tried conditional formatting but cannot get it right!! Col A Col.B Col. C smith 15/2/08 if after 18/2/08 then "bad", otherwise col. B TIA Terry- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
Appreciated Peter
Terry "Pete_UK" wrote in message ... In which case you can amend it further to: =IF(K3 $R$3, "LATE","") to get the same effect more efficiently. Hope this helps. Pete On Feb 11, 6:01 pm, "Terry" wrote: OK Peter Amended formula to include = along with <, , to rid the "false" kicking in. Terry |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date help please
You are correct David....Peter noticed too.
Thank you all. Terry "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... If by that last reply you mean that you've changed your formula from =IF(K3 $R$3, "LATE",IF(K3 < $R$3,"")) to =IF(K3 $R$3, "LATE",IF(K3 <= $R$3,"")) then you might want to have another think about how you would get to the second test in any situation where it would not be satisfied, remembering that you've already done the first test. In other words, why not just =IF(K3 $R$3, "LATE", "") ? If I take your reply literally and you've actually changed the formula to =IF(K3 = $R$3, "LATE",IF(K3 <= $R$3,"")) then tghere's even more confusion, in that if you've accepted the = case in the first test, you can't get to the second test with =. If = counts as late, then all you need is =IF(K3 = $R$3, "LATE", "") -- David Biddulph "Terry" wrote in message ... OK Peter Amended formula to include = along with <, , to rid the "false" kicking in. Terry "Pete_UK" wrote in message ... Just check what happens when K3 is the same date as R3. Pete On Feb 11, 5:17 pm, "Terry" wrote: I carried on searching for solution and came up with this one, which I can modify: =IF(K3 $R$3, "LATE",IF(K3 < $R$3,"")) Cells are adjusted to suit my new spreadsheet. I do apologise if any inconvenience guys. Terry"Terry" wrote in message ... Office 2003 Win XP Pro. I have a spreadsheet with A)col. "names" and B)col. "dates paid" My desire is to use another column (say C), with function to highlight "any date after a certain date has passed". I have tried conditional formatting but cannot get it right!! Col A Col.B Col. C smith 15/2/08 if after 18/2/08 then "bad", otherwise col. B TIA Terry- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dates - Need to display date one month prior to user-entered date | Excel Worksheet Functions | |||
| how to get the random date between the start date and the end date? | Excel Worksheet Functions | |||
| Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
| Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
| Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Linear Mode
