| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
EASY cond format Date??
Hi,
Cell B2 is a date unlocked Cell C3 TARGET DATE is locked: =DATE(YEAR(B2),MONTH(B2)-6,DAY(B2)) I know where the cond formatting is etc but can't figure out the format for I am trying to get the C3 cell to change colors by: If the date is within 14 days before the target date in C3 changes to yellow If the date is within 2 days before the target date in C3 changes to another color If the date is within TARGET Date or past in C3 changes to red Also, if D3 is checked then C3 is hidden. I work with MSAccess and haven't used excel much so I don't know how to put this in the proper formats. If someone know of an example I can look at that. Thanks!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
EASY cond format Date??
You can use a formula in CF like this
=NOW()-B3<14 -- Regards Ron de Bruin http://www.rondebruin.nl "lmv" wrote in message ... Hi, Cell B2 is a date unlocked Cell C3 TARGET DATE is locked: =DATE(YEAR(B2),MONTH(B2)-6,DAY(B2)) I know where the cond formatting is etc but can't figure out the format for I am trying to get the C3 cell to change colors by: If the date is within 14 days before the target date in C3 changes to yellow If the date is within 2 days before the target date in C3 changes to another color If the date is within TARGET Date or past in C3 changes to red Also, if D3 is checked then C3 is hidden. I work with MSAccess and haven't used excel much so I don't know how to put this in the proper formats. If someone know of an example I can look at that. Thanks!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
EASY cond format Date??
Sorry but I don't see how this answers my full question... I have no B3 cell
for one thing and the question has 4 different parts I understand this =NOW()-B3<14 But I don't understand how NOW()-C3<14 will not conflict with the other parts of my question and I don't know the format. If the date is within 14 days before the target date in C3 changes to yellow If the date is within 2 days before the target date in C3 changes to orange If the date is within TARGET Date or past in C3 changes to red Also, if D3 is checked then C3 is hidden. Please, could you be more detailed Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
EASY cond format Date??
Hi lmv
OK try this Condition1 =TODAY()-C3<=0 Red color Condition2 =TODAY()-C3<=2 Orange color Condition3 =TODAY()-C3<=14 Yellow color -- Regards Ron de Bruin http://www.rondebruin.nl "lmv" wrote in message ... Sorry but I don't see how this answers my full question... I have no B3 cell for one thing and the question has 4 different parts I understand this =NOW()-B3<14 But I don't understand how NOW()-C3<14 will not conflict with the other parts of my question and I don't know the format. If the date is within 14 days before the target date in C3 changes to yellow If the date is within 2 days before the target date in C3 changes to orange If the date is within TARGET Date or past in C3 changes to red Also, if D3 is checked then C3 is hidden. Please, could you be more detailed Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
EASY cond format Date??
Thanks for the response... since this C3 is a calculated date not a stored
date does that have anything to do with the situation. I tried the formulas you gave and they doen't work nothing happens. I really thought this was an easy question... I guess not. "Ron de Bruin" wrote: Hi lmv OK try this Condition1 =TODAY()-C3<=0 Red color Condition2 =TODAY()-C3<=2 Orange color Condition3 =TODAY()-C3<=14 Yellow color -- Regards Ron de Bruin http://www.rondebruin.nl "lmv" wrote in message ... Sorry but I don't see how this answers my full question... I have no B3 cell for one thing and the question has 4 different parts I understand this =NOW()-B3<14 But I don't understand how NOW()-C3<14 will not conflict with the other parts of my question and I don't know the format. If the date is within 14 days before the target date in C3 changes to yellow If the date is within 2 days before the target date in C3 changes to orange If the date is within TARGET Date or past in C3 changes to red Also, if D3 is checked then C3 is hidden. Please, could you be more detailed Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
EASY cond format Date??
Working here
Send me a small test workbook PRIVATE with the formula and the CF conditions in the formula cell. I look at it tomorrow after work then -- Regards Ron de Bruin http://www.rondebruin.nl "lmv" wrote in message ... Thanks for the response... since this C3 is a calculated date not a stored date does that have anything to do with the situation. I tried the formulas you gave and they doen't work nothing happens. I really thought this was an easy question... I guess not. "Ron de Bruin" wrote: Hi lmv OK try this Condition1 =TODAY()-C3<=0 Red color Condition2 =TODAY()-C3<=2 Orange color Condition3 =TODAY()-C3<=14 Yellow color -- Regards Ron de Bruin http://www.rondebruin.nl "lmv" wrote in message ... Sorry but I don't see how this answers my full question... I have no B3 cell for one thing and the question has 4 different parts I understand this =NOW()-B3<14 But I don't understand how NOW()-C3<14 will not conflict with the other parts of my question and I don't know the format. If the date is within 14 days before the target date in C3 changes to yellow If the date is within 2 days before the target date in C3 changes to orange If the date is within TARGET Date or past in C3 changes to red Also, if D3 is checked then C3 is hidden. Please, could you be more detailed Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
EASY cond format Date??
ok I sent you a test.xl
(I also still need to know how to make the date become invisible when the check is marked also) Thanks!! "Ron de Bruin" wrote: Working here Send me a small test workbook PRIVATE with the formula and the CF conditions in the formula cell. I look at it tomorrow after work then -- Regards Ron de Bruin http://www.rondebruin.nl "lmv" wrote in message ... Thanks for the response... since this C3 is a calculated date not a stored date does that have anything to do with the situation. I tried the formulas you gave and they doen't work nothing happens. I really thought this was an easy question... I guess not. "Ron de Bruin" wrote: Hi lmv OK try this Condition1 =TODAY()-C3<=0 Red color Condition2 =TODAY()-C3<=2 Orange color Condition3 =TODAY()-C3<=14 Yellow color -- Regards Ron de Bruin http://www.rondebruin.nl "lmv" wrote in message ... Sorry but I don't see how this answers my full question... I have no B3 cell for one thing and the question has 4 different parts I understand this =NOW()-B3<14 But I don't understand how NOW()-C3<14 will not conflict with the other parts of my question and I don't know the format. If the date is within 14 days before the target date in C3 changes to yellow If the date is within 2 days before the target date in C3 changes to orange If the date is within TARGET Date or past in C3 changes to red Also, if D3 is checked then C3 is hidden. Please, could you be more detailed Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mileage Claim Formula | New Users to Excel | |||
| Date Format problem | Excel Discussion (Misc queries) | |||
| auto date: cond. format Question | Excel Discussion (Misc queries) | |||
| Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
| Excel enters date as a text format | Excel Discussion (Misc queries) | |||
Linear Mode
