Formula with dates in COLOR
 

On August 21, I submit a question regarding formulas with dates. Now I need
to color each one of the codes that I obtained with the formula.
My results of this problem :
M-00
M-01
M-02
M-03
M-04
M-05

etc.....

I need that each one have a color but for all the row that contains that
code in certain columns.

Thanks
Loulou

LAST QUESTION AND ANSER REGARDING THE SAME PROBLEM:

Assuming your dates are in A2 and onward, in B2:
="M"&TEXT(MONTH(TODAY())-MONTH(A2)+(YEAR(TODAY())-YEAR(A2))*12,"+00;-00;-00")

This will detect how far each month is away from today's date, and format it
match your coding standards.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"loulou" wrote:

I have different dates that I need to classified with different codes. The
codes are M-00, M-01, M-02, M-03, M-04, M-05, M-06, M-07, M-08, M-09, M-10,
M-11, M-12, M+01, M+02, M+03, M+04, M+05, M+06, M+07, M+08, M+09, M+10, M+11,
and M+12.
To gave a code this is the criteria:
Today's date is M-00, but is not only for today's date is for all the month
that today's date contained.
For example: 8/21/09 is M-00, but also for all the month of August will be
M-00.

Now this is the trick, as soon as September starts, September will be M-00.

M-00 means that we are on the month that we need to accomplish the task
M-01 means that we are 1 month away to acomplish the task
M-02 means that we are 2 months away to acomplish the task
M-03 .........
Now,
M+01 means that we are one month behind of that task
M+02 means that we are two months behind of the task
M+03 .......

Let me expose you some examples of code assigned already assuming that we
are on August as M-00.

8/5/09 M-00
8/20/09 M-00
9/6/09 M-01
10/5/09 M-02
11/9/09 M-03
12/12/09 M-04....................
7/09/09 M+01
6/8/09 M+02
5/5/09 M+03

Next month, August will be M+01 and September will be M-00.

How can I create a formula that as soon as change the today's date all the
codes change automatically.

Thank you
Loulou

Formula with dates in COLOR
 

Hello again loulou,

How exactly do you want the codes colored? Does each code need its own
unique color identifier? Assuming a basic structure where negative values
need to be red, and positive values are green...

Select the entire row you want formatted. Goto Format - Conditional
Formatting.
Change first dropdown to "fomrula is". Input the following:
=MID($A2,2,1)="-"
Click the format button, go to pattern, choose red. Out.
Add a condition.
Formula is:
=MID($A2,2,1)="+"
Click the format button, go to pattern, choose red. Out.

Hit Ok.
(Note that I am assuming the date codes are in column A. Change letter as
appropriate).

If this is not what you were looking, or something similar, could you
explain in more detail what colors you want, and under what conditions they
would be applied? Thanks.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"loulou" wrote:

On August 21, I submit a question regarding formulas with dates. Now I need
to color each one of the codes that I obtained with the formula.
My results of this problem :
M-00
M-01
M-02
M-03
M-04
M-05

etc.....

I need that each one have a color but for all the row that contains that
code in certain columns.

Thanks
Loulou

LAST QUESTION AND ANSER REGARDING THE SAME PROBLEM:

Assuming your dates are in A2 and onward, in B2:
="M"&TEXT(MONTH(TODAY())-MONTH(A2)+(YEAR(TODAY())-YEAR(A2))*12,"+00;-00;-00")

This will detect how far each month is away from today's date, and format it
match your coding standards.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"loulou" wrote:

I have different dates that I need to classified with different codes. The
codes are M-00, M-01, M-02, M-03, M-04, M-05, M-06, M-07, M-08, M-09, M-10,
M-11, M-12, M+01, M+02, M+03, M+04, M+05, M+06, M+07, M+08, M+09, M+10, M+11,
and M+12.
To gave a code this is the criteria:
Today's date is M-00, but is not only for today's date is for all the month
that today's date contained.
For example: 8/21/09 is M-00, but also for all the month of August will be
M-00.

Now this is the trick, as soon as September starts, September will be M-00.

M-00 means that we are on the month that we need to accomplish the task
M-01 means that we are 1 month away to acomplish the task
M-02 means that we are 2 months away to acomplish the task
M-03 .........
Now,
M+01 means that we are one month behind of that task
M+02 means that we are two months behind of the task
M+03 .......

Let me expose you some examples of code assigned already assuming that we
are on August as M-00.

8/5/09 M-00
8/20/09 M-00
9/6/09 M-01
10/5/09 M-02
11/9/09 M-03
12/12/09 M-04....................
7/09/09 M+01
6/8/09 M+02
5/5/09 M+03

Next month, August will be M+01 and September will be M-00.

How can I create a formula that as soon as change the today's date all the
codes change automatically.

Thank you
Loulou

Formula with dates in COLOR
 

Read Help on Conditional Formatting
Come back with more questions but DO tell us the version of Excel you have
2003 (or earlier) or 2007
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"loulou" wrote in message
...
On August 21, I submit a question regarding formulas with dates. Now I
need
to color each one of the codes that I obtained with the formula.
My results of this problem :
M-00
M-01
M-02
M-03
M-04
M-05

etc.....

I need that each one have a color but for all the row that contains that
code in certain columns.

Thanks
Loulou

LAST QUESTION AND ANSER REGARDING THE SAME PROBLEM:

Assuming your dates are in A2 and onward, in B2:
="M"&TEXT(MONTH(TODAY())-MONTH(A2)+(YEAR(TODAY())-YEAR(A2))*12,"+00;-00;-00")

This will detect how far each month is away from today's date, and format
it
match your coding standards.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"loulou" wrote:

I have different dates that I need to classified with different codes.
The
codes are M-00, M-01, M-02, M-03, M-04, M-05, M-06, M-07, M-08, M-09,
M-10,
M-11, M-12, M+01, M+02, M+03, M+04, M+05, M+06, M+07, M+08, M+09, M+10,
M+11,
and M+12.
To gave a code this is the criteria:
Today's date is M-00, but is not only for today's date is for all the
month
that today's date contained.
For example: 8/21/09 is M-00, but also for all the month of August will
be
M-00.

Now this is the trick, as soon as September starts, September will be
M-00.

M-00 means that we are on the month that we need to accomplish the task
M-01 means that we are 1 month away to acomplish the task
M-02 means that we are 2 months away to acomplish the task
M-03 .........
Now,
M+01 means that we are one month behind of that task
M+02 means that we are two months behind of the task
M+03 .......

Let me expose you some examples of code assigned already assuming that we
are on August as M-00.

8/5/09 M-00
8/20/09 M-00
9/6/09 M-01
10/5/09 M-02
11/9/09 M-03
12/12/09 M-04....................
7/09/09 M+01
6/8/09 M+02
5/5/09 M+03

Next month, August will be M+01 and September will be M-00.

How can I create a formula that as soon as change the today's date all
the
codes change automatically.

Thank you
Loulou

Formula with dates in COLOR
 

I would like to colored the entire row for ecxample if in the column M+/M-
the value is M-06 I would like to put the entire row yellow, if the value is
M-5, I want it pink, if the value is A-2 I wanted blue and if the value is
M-01 I wanted green.

I have MS Excell 2007.
Thank you
Loulou

"Luke M" wrote:

Hello again loulou,

How exactly do you want the codes colored? Does each code need its own
unique color identifier? Assuming a basic structure where negative values
need to be red, and positive values are green...

Select the entire row you want formatted. Goto Format - Conditional
Formatting.
Change first dropdown to "fomrula is". Input the following:
=MID($A2,2,1)="-"
Click the format button, go to pattern, choose red. Out.
Add a condition.
Formula is:
=MID($A2,2,1)="+"
Click the format button, go to pattern, choose red. Out.

Hit Ok.
(Note that I am assuming the date codes are in column A. Change letter as
appropriate).

If this is not what you were looking, or something similar, could you
explain in more detail what colors you want, and under what conditions they
would be applied? Thanks.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"loulou" wrote:

On August 21, I submit a question regarding formulas with dates. Now I need
to color each one of the codes that I obtained with the formula.
My results of this problem :
M-00
M-01
M-02
M-03
M-04
M-05

etc.....

I need that each one have a color but for all the row that contains that
code in certain columns.

Thanks
Loulou

LAST QUESTION AND ANSER REGARDING THE SAME PROBLEM:

Assuming your dates are in A2 and onward, in B2:
="M"&TEXT(MONTH(TODAY())-MONTH(A2)+(YEAR(TODAY())-YEAR(A2))*12,"+00;-00;-00")

This will detect how far each month is away from today's date, and format it
match your coding standards.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"loulou" wrote:

I have different dates that I need to classified with different codes. The
codes are M-00, M-01, M-02, M-03, M-04, M-05, M-06, M-07, M-08, M-09, M-10,
M-11, M-12, M+01, M+02, M+03, M+04, M+05, M+06, M+07, M+08, M+09, M+10, M+11,
and M+12.
To gave a code this is the criteria:
Today's date is M-00, but is not only for today's date is for all the month
that today's date contained.
For example: 8/21/09 is M-00, but also for all the month of August will be
M-00.

Now this is the trick, as soon as September starts, September will be M-00.

M-00 means that we are on the month that we need to accomplish the task
M-01 means that we are 1 month away to acomplish the task
M-02 means that we are 2 months away to acomplish the task
M-03 .........
Now,
M+01 means that we are one month behind of that task
M+02 means that we are two months behind of the task
M+03 .......

Let me expose you some examples of code assigned already assuming that we
are on August as M-00.

8/5/09 M-00
8/20/09 M-00
9/6/09 M-01
10/5/09 M-02
11/9/09 M-03
12/12/09 M-04....................
7/09/09 M+01
6/8/09 M+02
5/5/09 M+03

Next month, August will be M+01 and September will be M-00.

How can I create a formula that as soon as change the today's date all the
codes change automatically.

Thank you
Loulou