Convert Date in mmm yyyy format

Hi Experts,



I use this formula to extract the month and year

=MONTH(A2)*28&" "&YEAR(A2)

but it gives me 28 2023 instead of 1 2023 (date in A2 is 1/9/2023)


I do not want to use =TEXT() at it will skew the sorting in the pivot table

Please help

Why do you expect 1 when you are multiplying the month (1 for Jan presumably) by 28 in the formula?

either;

=TEXT(a2,"m yyyy")

or CTRL-F1,more formats and format A2 as m yyyy

I use this formula for another column to extract the month and show it in the mmm format


Is their a way to extract mmm yyyyy from a date without using =TEXT?

Originally Posted by bhenlee

I use this formula for another column to extract the month and show it in the mmm format


Is their a way to extract mmm yyyyy from a date without using =TEXT?

Read post 3, pay attention to the bit after the word 'or'

I will need to use the date extracted in a pivot table.. if I used text its not going to be sorted properly and if i simply change the format it will show all the day in the month instead of showing just the month total

Group it in the pivot table.

Any of these options will produce text. If you need it sortable in date order, either produce the same day of the month for all dates in a month:

=DATE(YEAR(A2),Month(A2),1)

for example (or use EOMONTH), or use a format that will sort correctly like YYYY-MM-DD

Using real dates is generally better.

Post a sample file showing EXACTLY what you want. We are going round in circles.

How about including two columns in your data with formulae like:

=MONTH(A1)
&
=YEAR(A1)

And using those in your filters.

i need to extract the month and year from a date in A1..

I need it to be NOT in TEXT format so it will be automatically sorted in the pivot table

See Post 9.

Try this. See file.

Last guess without getting a sample file...

For sorting:

Formula:

Please Login or Register  to view this content.

The multiplication by 28 in Post #1 makes sense when you want to convert a month number to a name of a month.

Example 1:
A3: 4.
B3: =28*A3.
Format B3 as mmmm
==> Result B3: 112, but shows "April".

Example 2:
A3: 4.
B3: =TEXT(28*A3,"mmmm").
==> Result B3: "April".