subtract the numerical value from month

[michaelsmithgall]

Im afraid this is very simple and I am just drawing a complete blank. I have a process on my internal system that requires me to look up the two digit Month on a file and subtract the current months two digit value to arrive at a value we are going to use for additional calculation.

so we may have 02 in the system to reprsent Feb and today would be 04 to represent April. i need to subtract 04 from 02 and get 10.

What i would like is:
A1 to receive the system date from the user
A2 to automatically reflect the two digit code from this month
a3 to show the calculated new month code (02-04=10)

thank you in advance for any help you can provide

 

[Jonmo1]

Try this

User enters month number in A1

=MONTH(DATE(2011,A1-MONTH(TODAY),1))

the year is irrelevant...

 

[Scott Huish]

=month(date(1,a1-month(today()),1))

 

[michaelsmithgall]

Hotpepper, thanks that worked perfectly

 

[michaelsmithgall]

HotPepper, it worked fine but i ran acros one combination that caused it to fail. I had to alter your formula slightly so maybe i caused it. here is what i have now

=MONTH(DATE(1,B3-B4,1))

i essentiall removed the Today() funtion so that both fields would come from user input (decided to have them do it for other reasons)

it works fine until B3=1 and B4=12, try and see if you get a fail. all other combination i have tried seem to work except that.

Since B4 is this month I guess I have 8 months to figure it out before anyone is aware of this. LOL

 

[Scott Huish]

=month(date(2,b3-b4,1))

 

[michaelsmithgall]

Great. Thanks for the help I just left the office but will run when I get in. Thanks again