Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FORMATTING DATES IN FORMULAS | Excel Discussion (Misc queries) | |||
Dates and formulas | Excel Discussion (Misc queries) | |||
USING DATES IN FORMULAS | Excel Discussion (Misc queries) | |||
Using dates in formulas | Excel Worksheet Functions | |||
help with formulas and dates??? | Excel Discussion (Misc queries) |