Date data on opening Excel files turn numbers format on Numbers. Can not see date data properly

Yes, I also sometimes end up with the Excel date serial numbers in a column that should be dates..

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.

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

You can give feedback to Apple via Numbers > Provide Numbers Feedback in your menu.

SG

Hi SG,

Thanks. That will help me if the OP in Numbers for Mac (French) calls back with questions.

https://communities.apple.com/fr/thread/252755720

(I stole your formula).

Come and join me with https://www.deepl.com/translator

It is fun when Numbers for Mac (English) is slow.

Regards,

Ian.

I can confirm that if I enter 44409 into a cell and format as a Date/Time that it does not display correctly. Let's see if anyone else can confirm this. I am using mac OS X10.15.7 and Numbers version 11.0

If this is a common problem for your Excel file imports, there is a script that will change the index numbers back into dates. I believe it came from SGIII. I have posted it below and we can instruct you how to install and use it if it is of interest.

Other people have used LibreOffice to open and re-save problematic Excel files before importing. This seems to fix whatever was wrong. I believe LibreOffice also imports older Excel files that Numbers won't import at all, and you can save them in a format Numbers will open. So it can be used to solve two problems.

set startdate to date "Friday, January 1, 1904 at 12:00:00 AM"

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) * days
			end repeat
		end tell
	end tell
end tell

Badunit wrote:

I believe it came from SGIII.

A long time ago! This reminds me how tricky this date business can get. Excel on the Mac originally used the 1904 date system. Recent versions for the Mac now by default use the 1900 date system, but give the user the option (under Preferences > Calculation) to use the 1904 date system.

Assuming the 1900 date system is in use, as in the OP's example, then the script needs to be revised to:

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.

SG

Yellowbox wrote:

Hi SG,

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

Why the -2?

I was hoping I could just slip the adjustment of -2 in there without being asked to explain it!

The most convincing explanation is that the formula gives the wrong result without it!

I remember reading somewhere that Excel has always treated 1900 as a leap year, with 29 days in February of 1900. That seems only reasonable. After all, 1896 was a leap year and leap years come every fourth year, right?

Apparently NOT right! In the Gregorian calendar, a leap year is a year that is divisible by 4, UNLESS it is divisible by 100, in which case it is not a leap year unless it is also divisible by 400. So 1800 and 1900 were NOT leap years, while 2000 was a leap year.

Why -2 and not -1? I think that's because the formula starts with =DATE(1900,1,1) instead of =DATE(1899,12,31).

SG

And Microsoft blames it on Lotus 1-2-3 that had it wrong before them. They continued the mistake for compatibility with 1-2-3.

https://docs.microsoft.com/en-us/office/troubleshoot/excel/wrongly-assumes-1900-is-leap-year#:~:text=When%20Microsoft%20Multiplan%20and%20Microsoft%20Excel%20were%20released%2C,1-2-3%20and%20provide%20greater%20compatibility%20with%20Lotus%201-2-3.

Hi SG,

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

Why the -2?

Enquiring minds want to know 🤔

regards,

Ian.

Ian, I like the looks of DeepL Translator!

Badunit, and maybe Lotus 123 got it from SuperCalc or VisiCalc?

SG