Bookmarked How to change Google Sheets date format and convert dates to numbers and text by Natalia Sharashova (ablebits.com)I never cease to be surprised with what I can do with the QUERY formula in Google Sheets. I had a problem today where I wanted to match two datasets using the date column. The problem was that they were in two different formats. So I started searching. Low and behond, I discovered that you can in fact format using the QUERY format. The formula looks something like this:

=QUERY(A1:C7,"select * format B 'd-mmm-yy (ddd)'")

These are the available values:

• d | Day without a leading zero for 1-9 (i.e. 7)
• dd | Day with a leading zero for 1-9 (i.e. 07
• ddd | Day as an abbreviation (i.e. Wed)
• dddd | Day as a full name (i.e. Wednesday)
• m (if not preceded or followed by hours or seconds) | Month without a leading zero (i.e. 8)
• mm (if not preceded or followed by hours or seconds) | Month with a leading zero (i.e. 08)
• mmm | Month as an abbreviation (i.e. Aug)
• mmmm | Month as a full name (i.e. August)
• mmmmm | First letter of the month (i.e. A)
• y or yy | Two digit year (i.e. 19)
• yyy or yyyy | Full numeric year (i.e. 2019)

I know Ben Collins has spoken about formatting dates before, but I never knew there were all these options.

The post 📑 How to change Google Sheets date format and convert dates to numbers and text first appeared on Read Write Collect.