A tricky format question
 

Hello,
I asked the following question and received help however, I have a new
twist. Instead of having the due date in column (C) I want to have the
day of the month i.e.: 17 or 27. This way I can use this for any month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I would like
column (A) to have the name of the bill and column (C) the have the due
date.
When the due date is a week away I would like the cell in column (A) to
change colors. I know I should use conditional formatting for this but I
need help with the formula to calculate the seven days within the due date.

A tricky format question
 

=AND(C2=TODAY(),C2<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Hello,
I asked the following question and received help however, I have a new
twist. Instead of having the due date in column (C) I want to have the
day of the month i.e.: 17 or 27. This way I can use this for any month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I would like
column (A) to have the name of the bill and column (C) the have the due
date.
When the due date is a week away I would like the cell in column (A) to
change colors. I know I should use conditional formatting for this but I
need help with the formula to calculate the seven days within the due
date.

A tricky format question
 

Thank you Bob!

"Bob Phillips" wrote in message
...
=AND(C2=TODAY(),C2<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Hello,
I asked the following question and received help however, I have a new
twist. Instead of having the due date in column (C) I want to have the
day of the month i.e.: 17 or 27. This way I can use this for any month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I would like
column (A) to have the name of the bill and column (C) the have the due
date.
When the due date is a week away I would like the cell in column (A) to
change colors. I know I should use conditional formatting for this but I
need help with the formula to calculate the seven days within the due
date.

A tricky format question
 

Bob,

When the month changes will this formula still work without me changing the
due date?

For example: If I have a due date of 7/27 when the month changes to August,
I'm I going to have to change the

Due date to 8/27?

If so, is there a way I can do this without changing the date? Maybe by
using 27 instead of the full date or having the date change automatically?

Please clarify.

Digital2k

"Bob Phillips" wrote in message
...
=AND(C2=TODAY(),C2<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Hello,
I asked the following question and received help however, I have a new
twist. Instead of having the due date in column (C) I want to have the
day of the month i.e.: 17 or 27. This way I can use this for any month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I would like
column (A) to have the name of the bill and column (C) the have the due
date.
When the due date is a week away I would like the cell in column (A) to
change colors. I know I should use conditional formatting for this but I
need help with the formula to calculate the seven days within the due
date.

A tricky format question
 

Do you mean that you want to assume the today's month regardless of what
date is in the cell, and test using a 'made-up date?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Bob,

When the month changes will this formula still work without me changing
the
due date?

For example: If I have a due date of 7/27 when the month changes to
August,
I'm I going to have to change the

Due date to 8/27?

If so, is there a way I can do this without changing the date? Maybe by
using 27 instead of the full date or having the date change automatically?

Please clarify.

Digital2k

"Bob Phillips" wrote in message
...
=AND(C2=TODAY(),C2<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Hello,
I asked the following question and received help however, I have a new
twist. Instead of having the due date in column (C) I want to have the
day of the month i.e.: 17 or 27. This way I can use this for any month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I would
like
column (A) to have the name of the bill and column (C) the have the due
date.
When the due date is a week away I would like the cell in column (A) to
change colors. I know I should use conditional formatting for this but
I
need help with the formula to calculate the seven days within the due
date.

A tricky format question
 

Yes, Please show me the best way to handle this.
Thank You!
Digital2k

"Bob Phillips" wrote in message
...
Do you mean that you want to assume the today's month regardless of what
date is in the cell, and test using a 'made-up date?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Bob,

When the month changes will this formula still work without me changing
the
due date?

For example: If I have a due date of 7/27 when the month changes to
August,
I'm I going to have to change the

Due date to 8/27?

If so, is there a way I can do this without changing the date? Maybe by
using 27 instead of the full date or having the date change
automatically?

Please clarify.

Digital2k

"Bob Phillips" wrote in message
...
=AND(C2=TODAY(),C2<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Hello,
I asked the following question and received help however, I have a new
twist. Instead of having the due date in column (C) I want to have the
day of the month i.e.: 17 or 27. This way I can use this for any month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I would
like
column (A) to have the name of the bill and column (C) the have the
due
date.
When the due date is a week away I would like the cell in column (A)
to
change colors. I know I should use conditional formatting for this but
I
need help with the formula to calculate the seven days within the due
date.

A tricky format question
 

Try this

=AND(DATE(YEAR(C2),MONTH(TODAY()),DAY(C2))=TODAY( ),DATE(YEAR(C2),MONTH(TODA
Y()),DAY(C2))<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Yes, Please show me the best way to handle this.
Thank You!
Digital2k

"Bob Phillips" wrote in message
...
Do you mean that you want to assume the today's month regardless of what
date is in the cell, and test using a 'made-up date?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Bob,

When the month changes will this formula still work without me changing
the
due date?

For example: If I have a due date of 7/27 when the month changes to
August,
I'm I going to have to change the

Due date to 8/27?

If so, is there a way I can do this without changing the date? Maybe by
using 27 instead of the full date or having the date change
automatically?

Please clarify.

Digital2k

"Bob Phillips" wrote in message
...
=AND(C2=TODAY(),C2<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Hello,
I asked the following question and received help however, I have a
new
twist. Instead of having the due date in column (C) I want to have
the
day of the month i.e.: 17 or 27. This way I can use this for any
month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I would
like
column (A) to have the name of the bill and column (C) the have the
due
date.
When the due date is a week away I would like the cell in column (A)
to
change colors. I know I should use conditional formatting for this
but
I
need help with the formula to calculate the seven days within the
due
date.

A tricky format question
 

Thanks,
However, when I use this in the conditional format window I get an error
message: You may not use unions, intersections or array constants for
conditional formatting criteria.
I'm I using this formula correctly or in the right place?
Digital2k

"Bob Phillips" wrote in message
...
Try this

=AND(DATE(YEAR(C2),MONTH(TODAY()),DAY(C2))=TODAY( ),DATE(YEAR(C2),MONTH(TODA
Y()),DAY(C2))<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Yes, Please show me the best way to handle this.
Thank You!
Digital2k

"Bob Phillips" wrote in message
...
Do you mean that you want to assume the today's month regardless of
what
date is in the cell, and test using a 'made-up date?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Bob,

When the month changes will this formula still work without me
changing
the
due date?

For example: If I have a due date of 7/27 when the month changes to
August,
I'm I going to have to change the

Due date to 8/27?

If so, is there a way I can do this without changing the date? Maybe
by
using 27 instead of the full date or having the date change
automatically?

Please clarify.

Digital2k

"Bob Phillips" wrote in message
...
=AND(C2=TODAY(),C2<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Hello,
I asked the following question and received help however, I have a
new
twist. Instead of having the due date in column (C) I want to have
the
day of the month i.e.: 17 or 27. This way I can use this for any
month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I would
like
column (A) to have the name of the bill and column (C) the have the
due
date.
When the due date is a week away I would like the cell in column
(A)
to
change colors. I know I should use conditional formatting for this
but
I
need help with the formula to calculate the seven days within the
due
date.

A tricky format question
 

Never mind! I found the problem. there was a space between T ODAY.
Thanks Bob, You're the man!
Digital2k

"Digital2k" wrote in message
...
Thanks,
However, when I use this in the conditional format window I get an error
message: You may not use unions, intersections or array constants for
conditional formatting criteria.
I'm I using this formula correctly or in the right place?
Digital2k

"Bob Phillips" wrote in message
...
Try this

=AND(DATE(YEAR(C2),MONTH(TODAY()),DAY(C2))=TODAY( ),DATE(YEAR(C2),MONTH(TODA
Y()),DAY(C2))<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Yes, Please show me the best way to handle this.
Thank You!
Digital2k

"Bob Phillips" wrote in message
...
Do you mean that you want to assume the today's month regardless of
what
date is in the cell, and test using a 'made-up date?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Bob,

When the month changes will this formula still work without me
changing
the
due date?

For example: If I have a due date of 7/27 when the month changes to
August,
I'm I going to have to change the

Due date to 8/27?

If so, is there a way I can do this without changing the date? Maybe
by
using 27 instead of the full date or having the date change
automatically?

Please clarify.

Digital2k

"Bob Phillips" wrote in message
...
=AND(C2=TODAY(),C2<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Hello,
I asked the following question and received help however, I have a
new
twist. Instead of having the due date in column (C) I want to have
the
day of the month i.e.: 17 or 27. This way I can use this for any
month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I
would
like
column (A) to have the name of the bill and column (C) the have
the
due
date.
When the due date is a week away I would like the cell in column
(A)
to
change colors. I know I should use conditional formatting for this
but
I
need help with the formula to calculate the seven days within the
due
date.

A tricky format question
 

It's that darn newsgroup wrap-around again!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Never mind! I found the problem. there was a space between T ODAY.
Thanks Bob, You're the man!
Digital2k

"Digital2k" wrote in message
...
Thanks,
However, when I use this in the conditional format window I get an error
message: You may not use unions, intersections or array constants for
conditional formatting criteria.
I'm I using this formula correctly or in the right place?
Digital2k

"Bob Phillips" wrote in message
...
Try this


=AND(DATE(YEAR(C2),MONTH(TODAY()),DAY(C2))=TODAY( ),DATE(YEAR(C2),MONTH(TODA
Y()),DAY(C2))<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Yes, Please show me the best way to handle this.
Thank You!
Digital2k

"Bob Phillips" wrote in message
...
Do you mean that you want to assume the today's month regardless of
what
date is in the cell, and test using a 'made-up date?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Bob,

When the month changes will this formula still work without me
changing
the
due date?

For example: If I have a due date of 7/27 when the month changes to
August,
I'm I going to have to change the

Due date to 8/27?

If so, is there a way I can do this without changing the date?
Maybe
by
using 27 instead of the full date or having the date change
automatically?

Please clarify.

Digital2k

"Bob Phillips" wrote in message
...
=AND(C2=TODAY(),C2<=TODAY()+7)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Digital2k" wrote in message
...
Hello,
I asked the following question and received help however, I have
a
new
twist. Instead of having the due date in column (C) I want to
have
the
day of the month i.e.: 17 or 27. This way I can use this for any
month
without
changing the date each month.
I want to create a spreadsheet listing bills and due dates. I
would
like
column (A) to have the name of the bill and column (C) the have
the
due
date.
When the due date is a week away I would like the cell in column
(A)
to
change colors. I know I should use conditional formatting for
this
but
I
need help with the formula to calculate the seven days within
the
due
date.