google sheetsgoogle-sheets-dates
Quick questions with Google Sheets.
I'm trying to extract the year from a date using the YEAR() function.
In the first column, I formatted all cells as dates, but in the second column, when I use =YEAR() and reference the first column I get really unexpected results.
Any ideas?
Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this:
=TEXT(A2, "00")
I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot.
=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - "
& TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)
Here's a break-down description of the formula:
FILTER function will filter for all rows that have something.MONTH and DAY functions will extract the respective values
from the date.TEXT function will convert the value from point 2 into a pre-formatted STRING.ARRAYFORMULA function will apply all the above to the complete column (skipping the header).SORT function will sort the result, given by the
ARRAYFORMULA.The SORT function allows for sorting. If you do that via a column sort, the entry of the ARRAYFORMULA is taken into account as well and gets re-positioned, causing mayhem.
See example file I've created: sorting dates as text
I use this formula to convert the date time values IFTTT spits out to a useful format:
=VALUE(SUBSTITUTE(A1, " at ", " "))
Where A1 is the cell containing the date string
This gives you a decimal number that you can format as "Date Time".
Best Answer
The reason is that column is also formatted as a date. Format it as a number and you'll get what you're after.
Google Sheets stores dates as a number of days from 12/31/1899. Your
YEAR()is appropriately pulling out "2010" (for example) but is then converting it to a date. 2010 days after 12/31/1899 is 7/2/1905. (The big clue is that the two sample rows you show are seven years apart in the first column, then 7 days apart in the second column.)