Unable to Split the Year and Month from a Timestamp Field

[vk7]

Hello Excel Gurus,

I have attached a excel file, where I have a timestamp field.. Inorder to make a pivot out of the timestamp field, I need to split the timestamp field to have only year-mm therefore in another column, I am planning to have yyyy-mm field but I am unable to split this information from the timestamp. I tried using the formula '=TEXT(A1, "yyyy-mm")' but it doesn't help. Can someone help me out? The end goal is to figure out the total count for each month and year by looking at the timestamp.

 

[ETAF]

you can get the date using
=DATEVALUE(MID(A1,10,2)&"/"&MID(A1,7,2)&"/"&TRIM(LEFT(A1,5)))

or TEXT
=TRIM(LEFT(A1,5))&"-"&MID(A1,7,2)
BUT that will not work as a date - JUST as a text value

 

[vletm]

=MID(A1,2,10)*1 and copy as You need

 

[p45cal]

Add a column to convert your textual dates to real excel dates:
=DATEVALUE(LEFT(TRIM(A1),10))
then in your pivot you can group the dates into years and months.
In the attached, yor text values, converted to dates, pivot with grouping.

 

[vletm]

p45cal​

Oh just noticed that You've trimmed whole cell ... then left-function works well.
... but with mid and *1 ... it's shorter way.

vk7​

If You're using dates then please use dates (instead of something else).