Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date comparison
I am trying to come up with a formula to compare 2 dates and highlight the
cell if a certain result is reached. Here is the logic: IF RC[-2]-RC[-8]=3 IF RC[-8]<Thursday IF RC[-8]<Friday HIGHLIGHT RC[-1] ENDIF ENDIF ELSE IF RC[-2]-RC[-8]=5 IF RC[-8]<Monday IF RC[-8]<Tuesday IF RC[-8]<Wednesday HIGHLIGHT RC[-1] ENDIF ENDIF ENDIF ELSE HIGHLIGHT RC[-1] ENDIF It's been a while since my SPL class, so please excuse the mistakes. For non-programmers: I want to subtract 2 dates. If the result is 3 but the second cell's date is not a Thursday or Friday, to highlight it. If the result is 5 but the second cell's date is not Monday, Tuesday, or Wednesday, to highlight it. If there are any other results, highlight it. Note: I am using the R1C1 reference style. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date comparison
Anyone looking into this, or is it a pipe dream?
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date comparison
Bueller? Bueller?
"Darkdrew" wrote: Anyone looking into this, or is it a pipe dream? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date comparison
"Darkdrew" wrote in message
... I am trying to come up with a formula to compare 2 dates and highlight the cell if a certain result is reached. Here is the logic: IF RC[-2]-RC[-8]=3 IF RC[-8]<Thursday IF RC[-8]<Friday HIGHLIGHT RC[-1] ENDIF ENDIF ELSE IF RC[-2]-RC[-8]=5 IF RC[-8]<Monday IF RC[-8]<Tuesday IF RC[-8]<Wednesday HIGHLIGHT RC[-1] ENDIF ENDIF ENDIF ELSE HIGHLIGHT RC[-1] ENDIF It's been a while since my SPL class, so please excuse the mistakes. For non-programmers: I want to subtract 2 dates. If the result is 3 but the second cell's date is not a Thursday or Friday, to highlight it. If the result is 5 but the second cell's date is not Monday, Tuesday, or Wednesday, to highlight it. If there are any other results, highlight it. Note: I am using the R1C1 reference style. Darkdrew, To start with Formula to subract two dates, resuting in days: =TEXT(RC[-1]-RC[-2],"D") In conditional Formatting use =AND(RC="3",NOT(OR(TEXT(RC[-1],"DDDD")="Thursday",TEXT(RC[-1],"DDDD")="Friday"))) Format for highlighting The rest is up to you... Beege |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date comparison
I appreciate the response. I'll try it out tomorrow. It won't work right
now because I've found a new problem. My boss gave me the file as a CSV sheet. The dates are text and I need to convert them to date format. Mon, Jun 12, 06 = 6/12/06 I can get it to extract the 06 and maybe the 12, but the Jul is screwing it up mightily. I've seen a few on the group here, but they're not working as the examples are not in the same text-style mine are. Any tips for this one, too? "Beege" wrote: "Darkdrew" wrote in message ... I am trying to come up with a formula to compare 2 dates and highlight the cell if a certain result is reached. Here is the logic: IF RC[-2]-RC[-8]=3 IF RC[-8]<Thursday IF RC[-8]<Friday HIGHLIGHT RC[-1] ENDIF ENDIF ELSE IF RC[-2]-RC[-8]=5 IF RC[-8]<Monday IF RC[-8]<Tuesday IF RC[-8]<Wednesday HIGHLIGHT RC[-1] ENDIF ENDIF ENDIF ELSE HIGHLIGHT RC[-1] ENDIF It's been a while since my SPL class, so please excuse the mistakes. For non-programmers: I want to subtract 2 dates. If the result is 3 but the second cell's date is not a Thursday or Friday, to highlight it. If the result is 5 but the second cell's date is not Monday, Tuesday, or Wednesday, to highlight it. If there are any other results, highlight it. Note: I am using the R1C1 reference style. Darkdrew, To start with Formula to subract two dates, resuting in days: =TEXT(RC[-1]-RC[-2],"D") In conditional Formatting use =AND(RC="3",NOT(OR(TEXT(RC[-1],"DDDD")="Thursday",TEXT(RC[-1],"DDDD")="Friday"))) Format for highlighting The rest is up to you... Beege |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date comparison
"Darkdrew" wrote in message
... I appreciate the response. I'll try it out tomorrow. It won't work right now because I've found a new problem. My boss gave me the file as a CSV sheet. The dates are text and I need to convert them to date format. Mon, Jun 12, 06 = 6/12/06 I can get it to extract the 06 and maybe the 12, but the Jul is screwing it up mightily. I've seen a few on the group here, but they're not working as the examples are not in the same text-style mine are. Any tips for this one, too? "Beege" wrote: Try Data/Text to Columns Fixed Width Remove separators/Lines exc. between "Mon, " and "Jun Don't import "Mon " column TADA Beege |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date comparison
Unfortunately the entire date is in one column. I guess I can try to
manually delete them, but at that rate, I may as well rewrite the whole thing. "Beege" wrote: "Darkdrew" wrote in message ... I appreciate the response. I'll try it out tomorrow. It won't work right now because I've found a new problem. My boss gave me the file as a CSV sheet. The dates are text and I need to convert them to date format. Mon, Jun 12, 06 = 6/12/06 I can get it to extract the 06 and maybe the 12, but the Jul is screwing it up mightily. I've seen a few on the group here, but they're not working as the examples are not in the same text-style mine are. Any tips for this one, too? "Beege" wrote: Try Data/Text to Columns Fixed Width Remove separators/Lines exc. between "Mon, " and "Jun Don't import "Mon " column TADA Beege |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date comparison
Let's see if I can't rephrase things, because that formula doesn't work for me.
I have a date in column 12 and 18. Column 20 =RC[-2]-RC[-8]. I need to NOT highlight column 20 if 1) it =-3 AND column 12=Thursday or Friday. 2) it =-5 AND column 12=Monday, Tuesday, or Wednesday. For now I've been doing it manually, but I hope to have a solution to this by next week when I have to do it all over again. As for the text/date conversion, it's moot now. I copy/pasted from a version of the worksheet my boss gave me where it DOES have the dates instead of text. Thanks, though. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding XY days to date in cells | Excel Discussion (Misc queries) | |||
Another Date issue. | Excel Worksheet Functions | |||
Date comparison | Excel Discussion (Misc queries) | |||
Date Math Problem | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions |