converting Excel date numbers to dates

My previous attempt to post apparently didn't work. Please forgive any repetition:

You can also fix this by formula.

1) FIX with formula

With the date serial number in A2, you can convert to a Numbers date-time with this:

=DATE(1900,1,1)+A2−2

Replace the , in the formula with ; if your region uses , as a decimal separator.

Copy or fill that formula down the column.

Once converted, command-c to copy, Edit > Paste Formula results. Then delete the column with the date serial numbers.

2) FIX by script

Advantage: you don't have to add extra column, enter formula, and do housekeeping afterward.

Step 1: Copy-paste script below into Script Editor (in Applications > Utilities)

Step 2: Select the cells with the serial number dates

Step 3: Click the <run> button in Script Editor

If "nothing happens" make sure Script Editor is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

Since this changes the values in the cell I suggest testing on a copy of the table first.

set startdate to date "Monday, January 1, 1900 at 00:00:00"

tell application "Numbers" to tell front document
	tell active sheet
		tell (first table whose class of selection range is range)
			repeat with c in (get selection range)'s cells
				set value of c to startdate + ((value of c) - 2) * days
			end repeat
		end tell
	end tell
end tell

This is a later version of the script than the ancient one posted upthread.

SG

Usually when an Excel spreadsheet is imported the dates are converted to Numbers' date format but there are cases where that does not happen.

The following AppleScript from SGIII will convert selected cells from Excel serial number "dates" to Numbers' date format. You can run it from the Script Editor app or you can create a shortcut from it using the "run AppleScript" action (delete the default stuff in the action and copy/paste the script in its place).

It is valid for March 1, 1900 and up. Microsoft thought 1900 was a leap year (it was not) so they have a non-existent Feb 29, 1900.

tell application "Numbers" to tell the front document to tell active sheet to tell (first table whose class of selection range is range)
	repeat with c in (get selection range)'s cell
		tell c
			try
				if its value = (its value) / 1 then
					set its value to "=\"1/1/1900\"+" & (its value) & " - 2"
					set its value to (its value)
				end if
			end try
		end tell
	end repeat
end tell