Numbers not importing excel dates consistently

As you've discovered Numbers sometimes gets confused and uses the Excel serial number date. (Excel stores dates as a number based on days since the beginning of the 20th century, while Numbers uses a date-time string.)

To convert from the Excel serial date, you can use a formula like this:

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

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

Or you may find it more convenient to use an AppleScript, which doesn't require you to set up an extra column with a formula.

Here's the script:

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

To use, copy-paste into Script Editor (in Applications > Utilities), select cells with date serial numbers, and click the <run> button.

Test on a backup first.

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

If you expect to use the script again in the future you can save it in the Script Menu.

SG

Gino C wrote:

a bunch of extra effort to run the script every time just to watermark a pdf version and send to back.

A small bunch of extra work. You have to select cells and then run. That takes a few seconds.

However, if you haven't done so already, you might consider giving feedback to Apple via Numbers > Provide Numbers Feedback in your menu, explaining the problem you are having importing Excel dates. You are unlikely to receive a response. But feedback is reviewed. If there is enough feedback, a bug like this may be addressed in due course.

SG

Gino C wrote:

print this one client's regular orders from a different machine, two steps to the left. ;)

A, yes, the old two-step can work just as well! Even involves a little physical movement, generally a good thing;)

Maybe Apple will squash the bug before you have to update that other machine too.

SG

Gino C wrote:

While I now understand the issue I find it sad that I can open these files properly on all my older machines.

A few versions ago it seemed like a bunch of new bugs got introduced. There are still a few bugs that have not been fixed, though there have been multiple updates in the mean time to add new features. I know of four in particular that I have been watching and waiting on. One is a problem with extraneous labels on the X axis of some charts, another is about the WORKDAY function when dates are excluded, a major one for me has to do with how sorting messes up range references in functions such as COUNTIF, and a fourth is a problem with using dates in the SUMIFS function when the region is Canada. And now you are saying your older versions process these Excel files correctly while the latest does not. What version(s) are you running that do it correctly?

SGIII has a handy AppleScript to convert those numbers back to dates.