Date comparison

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darkdrew - 16 Mar 2006 22:08 GMT

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.

Reply to this Message

Darkdrew - 20 Mar 2006 15:49 GMT

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

Reply to this Message

Darkdrew - 22 Mar 2006 19:01 GMT

Bueller? Bueller?

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

Reply to this Message

Beege - 22 Mar 2006 20:04 GMT

>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:
[quoted text clipped - 26 lines]
>
> 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

Reply to this Message

Darkdrew - 22 Mar 2006 23:53 GMT

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?

> >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:
[quoted text clipped - 43 lines]
>
> Beege

Reply to this Message

Beege - 23 Mar 2006 00:05 GMT

>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
[quoted text clipped - 8 lines]
> one,
> too?

Try Data/Text to Columns
Fixed Width
Remove separators/Lines exc. between "Mon, " and "Jun
Don't import "Mon " column
TADA

Beege

Reply to this Message

Darkdrew - 23 Mar 2006 16:10 GMT

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.

> >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
[quoted text clipped - 16 lines]
>
> Beege

Reply to this Message

Beege - 23 Mar 2006 16:21 GMT

Darkdrew,

I tried
Mon, Jun 12, 06
Mon, Jul 1, 06

and a few others

It worked for me.
Have you tried it (use a backup to try it)?

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
[quoted text clipped - 25 lines]
>>
>> Beege

Reply to this Message

Darkdrew - 23 Mar 2006 17:22 GMT

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 to this Message

Beege - 23 Mar 2006 19:36 GMT

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

> Let's see if I can't rephrase things, because that formula doesn't work
> for me.
[quoted text clipped - 12 lines]
> instead
> of text. Thanks, though.

I'm a little confused.
The first post said if the difference was 3, then highlight. Now you seem to
say if minus 3, then don't highlight. Same with 5.
Sorry for being thick. A solution can be had, but the premise needs to be
solid. Are going to use conditional formatting to highlight the cells that
don't meet the requirements?

Beege

Reply to this Message

Beege - 23 Mar 2006 20:09 GMT

> Let's see if I can't rephrase things, because that formula doesn't work
> for me.
[quoted text clipped - 12 lines]
> instead
> of text. Thanks, though.

DarkDrew

Select Column 20
Format/Conditional Formatting
Formula is

=NOT(OR(AND(RC=-3,OR(TEXT(RC[-2],"DDDD")="Thursday",TEXT(RC[-2],"DDDD")="Friday")),AND(RC=-5,OR(TEXT(RC[-2],"DDDD")="Monday",TEXT(RC[-2],"DDDD")="Tuesday",TEXT(RC[-2],"DDDD")="Wednesday"))))

Highlight background or text to show up.

Hope this is what you need

Beege

Reply to this Message