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
|