| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Tricky Ranking Question | Excel Discussion (Misc queries) | |||
| Tricky counting question | Excel Discussion (Misc queries) | |||
| New guy with a tricky question | Excel Programming | |||
| Tricky Question | Excel Worksheet Functions | |||
| rota question - very tricky... | Excel Discussion (Misc queries) | |||
Linear Mode
