Google Sheets – How to Convert Unix Timestamp to Human Readable Date

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.

Formula

=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - " 
 & TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)

Explained

Here's a break-down description of the formula:

1. The FILTER function will filter for all rows that have something.
2. The MONTH and DAY functions will extract the respective values from the date.
3. The TEXT function will convert the value from point 2 into a pre-formatted STRING.
4. The ARRAYFORMULA function will apply all the above to the complete column (skipping the header).
5. The 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.

Screenshot

Example

See example file I've created: sorting dates as text

Your task is better described as matching or lookup rather than filtering: you want at most one entry to be returned. This can be efficiently done with vlookup

=arrayformula(iferror(vlookup('SHEET 1'!A2:A, 'SHEET 2'A2:A, 1, False)))

This looks up every element of 'SHEET 1'!A2:A in the column 'SHEET 2'A2:A and returns what is found. The arrayformula part ensures that everything is handled at once.

You may still run into performance problems, in which case you should either get rid of any empty rows at the bottom of the sheet (they are being looked up, too) or to filter out the blanks. That is, replace:

• 'SHEET 1'!A2:A with filter('SHEET 1'!A2:A, len('SHEET 1'!A2:A))
• 'SHEET 2'!A2:A with filter('SHEET 2'!A2:A, len('SHEET 2'!A2:A))

I'll add some remarks on the script, although I haven't studied it much.

• Use getLastRow() instead of getMaxRows(), so that the loop runs only over the rows with data. Same for columns.
• Do not call setValue in a loop. Instead, store the values you want to insert in a suitable array output, and after the loop call setValues(output). You can see such an example here.