Date comparison

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Multiply and Sum 2
SUMIF on a collection of individual cells 9
How to index every line 1
conditonal formatting in VB 2
Using myval as counter twice HELP 1
The code below worked for a few days and just totally stoppedworking. 1
IF function with 14 nested levels 3
Countif Function?? 3

Back
Top