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. |
Date comparison
Anyone looking into this, or is it a pipe dream?
|
Date comparison
Bueller? Bueller?
"Darkdrew" wrote: Anyone looking into this, or is it a pipe dream? |
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 |
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 |
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 |
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 |
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. |
All times are GMT +1. The time now is 05:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com