Return a number depending on the date in another cell

I need to return a number in column AH depending on the date in column H.

For example, the date displayed is in the format "20120401" YYYYMMDD. I need column AH to show "01" for the month of April, 02 for May, etc...

Do you need a formula to do this or a VBA (Macro) solution?

If you can hardcode the values into the formula this might work. You can adapt it to include every month if needed so you can just drag the formula down...

=IF(MONTH(B1)=4,"01")

You will need to replace "B1" with whatever cell you are referencing.

This will return the MM numbers

=MID(H1,5,2) - 0

If you want April (which is usually month 4) to be "01", you may have to subtract 3

=MID(H1,5,2) - 3

Format the cells with this formula as 00 if you want two retain the two digit format.

Sorry I should have mentioned that it needs to be VBA as I cannot have formulas in any cells.

Create 2 helper columns which uses the month by its number then add the number you want to show in the next column you can then use the following formula.

Please Login or Register  to view this content.

I have used this as example

Please Login or Register  to view this content.

I need code to do the task and add it into a macro I have already created.

Please Login or Register  to view this content.

That is just returning

HTML Code: 

in the cell rather than the actual number

I assigned this code to a button and it worked, although you'll need to change the sheet name/number if it's something other than sheet 1, and the ranges if they are different.

There are more efficient ways of writing this code, but this should be close to what you need.

Please Login or Register  to view this content.

As far as ranges go it differs every time i run the macro. I need it to only return the number dependant on whether or not column H contains a date.

I also get an error when I try to run this. Run time error 424. Object required with the following line debugged.

HTML Code: 

Originally Posted by luke.guthrie

That is just returning

HTML Code: 

in the cell rather than the actual number

If column AH was formatted as text, you could get the formula instead of the month number.

This should avoid that problem.

Please Login or Register  to view this content.

That is returning the proper format but the numbers area bit off. For example April and May are showing as 00 when it should be 01 and 02 respectively, March is showing up as 04 when it should be 12, June is showing as 01 instead of 03

I just need the code to focus on the "MM" in the string of YYYYMMDD

Please Login or Register  to view this content.

Still a little off. April and May are both 09 instead of 01 and 02. June is 10 instead of 03, etc

Originally Posted by luke.guthrie

April and May are both 09 instead of 01 and 02.

I don't understand how two different months could return the same month number. Can you list several examples of your data and the expected results. I'm guessing the date format in column H is not exactly as I understand it.

Are the dates in column H actual serial dates formatted as YYYYMMDD and not just a number or text? If they are serial dates, try this...

.FormulaR1C1 = "=MONTH(EOMONTH(RC[-26],-3))"

The formatting of the dates is a custom value, entered as DD/MM/YYYY and shows up in the cell as YYYYMMDD.

20120401 09 (should be 01)
20120501 09 (should be 02)
20130301 01 (should be 12)

20120401 09
20120501 09
20130301 01

What are the red values? That's not part of a date formatted as YYYYMMDD

red values are whats being returned in column AH

Then if column H are dates, this should work.

.FormulaR1C1 = "=MONTH(EOMONTH(RC[-26],-3))"

perfect thanks!