Extract month and year from a cell

Hi everyone,

My 1st post here

I am trying to extract a month into one column and a year into the next column. First, the month extraction attempt:

Book Date Created Date Formula used Result received Result should be
10/13/2016 9/27/2015 =TEXT(G2, "mm") 10/13/2016 10


It fails to give me the expected result ("10") and I am not sure where things are going wrong. Both source and result cell are formatted as General.

If I try to use =MONTH(G2) as another extraction option, then the result becomes #VALUE!. Stepping through the calculation steps shows:
"The cell currently being evaluated contains a constant"


Many thanks in advance for any assistance provided.


Cheers,
Wojtek

Please Login or Register  to view this content.

This formula should show the result 10.

Please replace the above formula with that and let me know what's the result now?

Please Login or Register  to view this content.

Hi,
After typing
=DATE(RIGHT(G2,4),LEFT(G2,2),MID(G2,4,2))

the result is
13/10/2016

Hi,

Typing
=month(datevalue(G2))
=year(datevalue(g2))

results in
#VALUE!


I will upload the sample worksheet in a sec.

Hi,

All I am getting when trying to attach the file is a blank white field next to the paper clip. Are there pop ups on this forum?

Something is odd - tried Chrome and Firefox:
Forum error.JPG

OK thanks, file should be uploaded now.

Hi Ali,

Thank you. When I change the data around from 10/13/2016 to 13/10/2016 then the correct value is shown.

Trouble is that the data source must be in US format and it looks like my Excel is set for the AU region (which it needs to be as well).

So at some point the regional format conversion needs to be accounted for.

Cheers,
W


A further example with results that shows the limited cells with the correct output:

Book Date Created Date Formula used Result received
10/13/2016 9/27/2015 =TEXT(G2, "mm") 10/13/2016
11/01/2016 11/18/2015 =TEXT(G2, "mm") 01
12/18/2016 2/23/2016 =TEXT(G2, "mm") 12/18/2016
9/13/2016 2/29/2016 =TEXT(G2, "mm") 9/13/2016
11/30/2016 3/01/2016 =TEXT(G2, "mm") 11/30/2016
10/13/2016 3/16/2016 =TEXT(G2, "mm") 10/13/2016
9/23/2016 4/12/2016 =TEXT(G2, "mm") 9/23/2016
11/28/2016 4/21/2016 =TEXT(G2, "mm") 11/28/2016
12/22/2016 4/21/2016 =TEXT(G2, "mm") 12/22/2016
9/30/2016 5/04/2016 =TEXT(G2, "mm") 9/30/2016
9/13/2016 6/09/2016 =TEXT(G2, "mm") 9/13/2016
10/13/2016 6/10/2016 =TEXT(G2, "mm") 10/13/2016
8/13/2016 6/10/2016 =TEXT(G2, "mm") 8/13/2016
10/13/2016 6/10/2016 =TEXT(G2, "mm") 10/13/2016
10/13/2016 6/10/2016 =TEXT(G2, "mm") 10/13/2016
8/04/2016 7/12/2016 =TEXT(G2, "mm") 04
8/31/2016 7/12/2016 =TEXT(G2, "mm") 8/31/2016
8/04/2016 7/20/2016 =TEXT(G2, "mm") 04

Originally Posted by Wojtek-AU

A further example with results that shows the limited cells with the correct output:

Book Date Created Date Formula used Result received
10/13/2016 9/27/2015 =TEXT(G2, "mm") 10/13/2016
11/01/2016 11/18/2015 =TEXT(G2, "mm") 01

But it is not correct, if your data is in US format, then the second row should return November, not January!

Go to the Data tab on the excel ribbon.

Select column A (the entire column)

Select Text to Columns on the ribbon.

Click Next twice

Change the Date dropdown to MDY

Click Finish.

Repeat for Column B.

Now you should have the correct results.

I have no idea, Ali! I thought you would be far more observant :eek

I noticed your post with the links after submitting mine, and thought that you were suggesting the same thing.

Guess I need to have a look at them now to see what the alternatives are

The text to columns method seems to be the most reliable.

Some of the other methods appear to get confused when some of the dates are recognisable in both MDY and DMY formats, i.e. when the day is 12 or less.

This particular thread could also be solved with a fairly simple formula,

=IFERROR(DAY(A2),--LEFT(A2,FIND("/",A2)-1))

However, even the slightest inconsistency in the source data, such as dates delimited with - instead of / will cause the formula to fail, where text to columns will still work correctly.

Originally Posted by Wojtek-AU

OK thanks, file should be uploaded now.

I have checked your sample. You are facing the problem with date formatting. On your sample only A3 is in date formats and all others are in General format. If you want to keep this then use the below formula.

Please Login or Register  to view this content.

Sanram, please try to avoid posting inaccurate information.

Your observation on formatting and your formula are both incorrect.

Dates formatted as general are still recognised as dates, if the general format was the problem then the dates would show as numbers. This is completely different to regional formatting, which is the actual cause of the problem.

This is also why your formula is incorrect. When the dates are in the wrong regional format, in the case of this thread, some dates are recognised, but the month and day get swapped around incorrectly, which is why my example of a possible formula uses DAY instead of MONTH to return the correct month of the valid dates.

Hi all,

First of all, many thanks to everyone for trying to help me.

Secondly, Ali, thank you for prompting me with Qs and for the guidance on attaching files etc. As a n00b on this forum, even the most basic of tasks seemed complex...thank you for the links but due to the amount of the info they contained I did not get around to read them all just yet.

And then comes Jason. I can't believe the simplicity of your solution! You have no idea what my workarounds have been till now (let's just say I had been pasting data into notepad & then back again!).

Is there a button I can click to show it as the preferred answer?

Once again, many thanks to all of you. The forum is what it is as a result of such collaboration


Cheers,
Wojtek

Hi Wojtek,

Thanks for the feedback!

There is no option to flag an answer as preferred, however we do ask that you mark your thread as Solved. This can be done from the Thread Tools drop down, which you will find on the right side of the page just above post #1.

If this is something that you do on a regular basis then you could try recording a macro while carrying out the steps I suggested, then next time you just need to run the macro and that will do the rest for you.

One limitation of the macro recorder though, is that it repeats exactly the same actions, so if the dates might be in different columns next time then it will not work correctly.

Going a step further, code can be written to allow for differing sheet layouts if we can identify a pattern in the sheets to enable the code to find the date columns.

Another alternative would be to correct the data when it is imported from the external data source, but that is where things can start to get a bit more complicated.