1

How do I convert the text "Oct-14" to date, so that it's being read as "Oct 01, 2014. I tried to go to Format > Date and selected the format: Oct-2014, but google sheets is interpreting it as "Oct 14, 2016". I just need the month and year (Oct-2014).

Here's the sample spreadsheet: I need help with columns B and C.

1

You can use

=datevalue("01-"&B2)

(single-cell formula that can be copied down), or

=arrayformula(datevalue("01-"&B2:B))

(converts an entire column at once), or

=arrayformula(iferror(datevalue("01-"&B2:B)))

(does the same, while suppressing any error messages).

This works because the strings like 01-Oct-14 are understood by datevalue command correctly, at least in your locale.

  • Awesome! thanks so much Live Forever! – Vee Feb 3 '16 at 5:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.