Apple Numbers

I'm not sure if there's a simpler way to do this, but the numbers are indeed Excel date serial numbers, counting days from a date that's supposed to be 01 January 1900. It appears though that Microsoft (and Lotus before them) missed out a couple of leap years, so as far as I can tell from online examples, the dates have to be calculated from 30 December 1899.

To convert the dates using Pages:

Insert a column to the right of the date serial numbers (my first column is B, my inserted column is C).

In the first non-header cell (C2 in my example), insert the following formula:

DATE(1899,12,30)+B2

This adds the number of days in B2 to the origin date in 1899. Press enter to confirm.

I am using a UK date format (DD/MM/YYYY) to format column C.

You would need to verify your data against the original Excel spreadsheet to make absolutely sure that the starting date is correct - I am not absolutely certain that December 30 1899 is correct.

Cell C2 can then be filled down to format the dates for the remaining serial numbers.

Once you're happy that the dates are correct, you can delete the column containing the serial numbers.

If that seems a bit cumbersome you may be better off investigating a reformat of the dates in Excel, or using an alternative productivity suite like OpenOffice or LibreOffice, both of which are available for macOS.

Alternatively, the following AppleScript will ask for the name/letter of the column with the serial numbers, insert an additional column to the right and fill down the calculated dates. Again, you might need to double-check my presumed start date of December 30 1899. Click on the table to select it before running the script.

tell application "Numbers"
	tell document 1
		tell sheet 1
			set active_table to first table whose selection range's class is range
			tell active_table
				set headerCount to header row count
				set footerCount to footer row count
				set rowCount to count rows
				set columnNames to name of every column
				set startDate to date "Saturday, 30 December 1899 at 00:00:00"
				set serialColumn to (choose from list columnNames with prompt "Which column contaiins the date serial numbers?") as string
				if serialColumn is false then return --  user cancelled
				set dateColumn to (add column after column serialColumn)
				repeat with theRow from (headerCount + 1) to (rowCount - footerCount)
					set nextSerial to value of (cell theRow) of (column serialColumn)
					set calculatedDate to startDate + nextSerial * days
					set value of cell theRow of dateColumn to calculatedDate
				end repeat
			end tell
		end tell
	end tell
end tell

You'd need to reformat the added column to date-only.

popchart wrote:

Thank you so much for your reply. I have tried out the formula which seems to produce a date 7 days ahead of the actual date but by swapping 1899, 12, 23 for 1899, 12,30 the correct date is displayed.

How strange. Out of interest, could you post a couple of numbers with the dates you know to be correct?

HD wrote:

To convert the dates using Pages:

Sorry, Numbers not 'Pages'.

Phew 😁