Date comparison

D

Darkdrew

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.
 
B

Beege

Darkdrew said:
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.
Click to expand...


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
 
D

Darkdrew

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 said:
Darkdrew said:
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.
Click to expand...


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
Click to expand...
 
B

Beege

Darkdrew said:
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:
Click to expand...

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

Beege
 
D

Darkdrew

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.
 
B

Beege

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
 
D

Darkdrew

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.
 
B

Beege

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


Darkdrew said:
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.
Click to expand...

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
 
B

Beege

Darkdrew said:
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.
Click to expand...

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
 
Top