Formulas with dates
 

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

Formulas with dates
 

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

Formulas with dates
 

It works Luke, can you please explain me how the formula works. Just work
great!
Thanks again Luke!
Loulou

"Luke M" wrote:

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

Formulas with dates
 

Glad it works!
Explaination ("...." will refer to part of formula already discussed):
MONTH(TODAY())-MONTH(A2)
Take the month of today, and subtract the month from A2. This gives the base
amount of how far away the date is.

.....+(YEAR(TODAY())-YEAR(A2)*12
Next, need to take into account dates that are in a different year (so it
won't count Jan 2010 as being 7 months in past). Take Year of today - year of
A2, and multiply by 12 months/year. Add this to base amount.

TEXT(.....,"+00;-00;-00")
Convert result of formula into a 2 digit number (just like custom number
format). The semicolons seperate how to handle positive/negative/zero values,
respectively. So, positive values get a "+" symbol, all the rest get "-"
symbol.

="M"&....
Concacatenate the letter "M" onto the front of the value returned from the
TEXT function.


--
Best Regards,

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


"loulou" wrote:

It works Luke, can you please explain me how the formula works. Just work
great!
Thanks again Luke!
Loulou

"Luke M" wrote:

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