You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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

Date data on opening Excel files turn numbers format on Numbers. Can not see date data properly. It was not like this before, I had no problem seeing correct datas on each cells. Pls follow below sample.


Example: Excel file: 1/8/21 , Numbers same cell : 44409.

MacBook Air 13″, macOS 10.15

Posted on May 11, 2021 2:06 AM

Reply
Question marked as Top-ranking reply

Posted on May 11, 2021 5:29 AM

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

10 replies
Question marked as Top-ranking reply

May 11, 2021 5:29 AM in response to tgbac208

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

May 11, 2021 7:40 AM in response to tgbac208

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

May 11, 2021 8:47 AM in response to Badunit

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

May 13, 2021 8:18 AM in response to Yellowbox

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


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

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.