Formulas with dates

[loulou]

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

 

[Luke M]

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.

 

[loulou]

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

 

[Luke M]

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.