Convert Date with Time to only Month / Year (non text form)

[squeakums]

I need to Convert Date with Time to only Month / Year (non text form) so that I can sort the dates in a pivot table later. Cell e4 has the full date with time data.

 

[steve the fish]

Use formatting. Take a look in the custom formatting options.

 

[alansidman]

Do you need to convert it or reformat it. If you convert it then it will be text. If you reformat it then it is showing only as mm/yy and the underlying value is still the numerical.

 

[Toadstool]

Hi Squeakums,

You can't have an Excel date as non-text without a day so I have two suggestions.

1. Use text year and month which will still sort correctly (F4 example)
2. Convert to Excel date always using 1st of the month (F5 example)

Book2
	E	F
4	3/3/2021 17:45	2021 03
5	3/3/2021 17:45	3/1/2021
Sheet1

Cell Formulas
Range	Formula
F4	F4	=TEXT(E4,"yyyy mm")
F5	F5	=DATE(YEAR(E5),MONTH(E5),1)

 

[squeakums]

Issue is once I pivot this it won't sort the month / year correctly using text form.

 

[Joe4]

If the issue is that you want to sort/group them by just year and month (and ignore day and time), you could insert a column and make all the days equal to the first day of the month, and remove the time piece. You can then format it to only show month/year, and if you use this new field for grouping and sorting, it should do what you want.

For a date/time in cell A1, the formula will look like:

=DATE(YEAR(A1),MONTH(A1),1)