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.

Anyone looking into this, or is it a pipe dream?

Bueller? Bueller?

"Darkdrew" wrote:

> Anyone looking into this, or is it a pipe dream?

"Darkdrew" <Darkdrew@discussions.microsoft.com> wrote in message
news:F39AA012-1FEC-4FDC-9B32-9E7B84FFA55D@microsoft.com...
>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

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" <Darkdrew@discussions.microsoft.com> wrote in message
> news:F39AA012-1FEC-4FDC-9B32-9E7B84FFA55D@microsoft.com...
> >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
>
>
>

"Darkdrew" <Darkdrew@discussions.microsoft.com> wrote in message
news:E865BE04-B970-4773-8195-EC444FC1C050@microsoft.com...
>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

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" <Darkdrew@discussions.microsoft.com> wrote in message
> news:E865BE04-B970-4773-8195-EC444FC1C050@microsoft.com...
> >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
>
>
>

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.