importing Excel to Numbers and date format

Question

Level 1

46 points

importing Excel to Numbers and date format

I hope this helps some one. In Excel, if I take the date April 14, 2013, I can format the cell to show as a number and get the number 39916. (I have set preferences to use the 1904 date system). If I enter just the number 39916, then format the cell to show in date I get April 14, 2013 (or what ever format chosen). However in Numbers you can't do that, a number can't be formated to show a date, or visa-a-versa! Correct me if I am wrong. However I do note that =Year(39916) does give 2013. You can do math and reference another cell and get the correct date, like =A1 + 7*15 and get April 14, 2013 if cell A1 is December 30, 2012. However = 39811 + 7*15 can be formated in Excel to get April 14, 2013, but it can't be in Numbers, it just gives 39916. In Numbers you have to start with a date.

So what I have used that works in both Excel and Numbers is use the functions DATE or DATEVALUE. Such =DATE(2013, 12, 30) + 7*15 can be formated as a date in both Excel and Numbers.

I think this is a problem with Numbers.

Posted on Apr 24, 2013 12:59 PM

Reply

Answer 1

Apr 24, 2013 1:56 PM in response to Paul Hillman

Paul,

Numbers is not MS Excel. It has many overlapping features with Excel but is not, by definition, required to support any particular feature of Excel. If you want to make a suggestion to Apple (the developer of Numbers) you can provide feedback by using the menu item (in Numbers) "Numbers > Provide Numbers feedback".

It is good that you posted a common solution between the two spreadsheet programs.

Regards,

Wayne

Reply

Answer 2

Paul Hillman Author

Level 1

46 points

Apr 24, 2013 10:50 PM in response to Wayne Contello

Wayne,

You're right, Numbers is not Excel, but they are both spreadsheet programs (you said it yourself). As a spreadsheet program you would expect some standard features. Numbers imports Excel spreadsheets, You'd expect that certain 'basic' operations to import, and I'd expect basic math operations to import better than fancy formating or advanced operations. Note there are quite a few threads here about importing problems, and in particular dates. Instead of trying to post to each thread I started my own.

In particular, if I have a valid number in a cell, and go to the inspector and tell it to display this number as a date, that Numbers should do it. Numbers gives no error, it just displays the number. Numbers does recognize the number as a valid date value, as if you say =Year(39916) you get 2013, =month(39916) you get 4, and =day(39916) you get 14. But putting 39916 in a cell and telling Number to display the number in a date format you just get 39916 displayed in the cell. So I don't understand the inspector and choosing a format to display a cells contents, I think its broken.

Everything a computer stores is just a string of binary digits. We tell the computer to intrepret and display this string starting starting at this digit and ending at another as an octal number, base 10 number, text, a color, an image, a song, a date, etc. Obviously Number recognizes 39916 as April 14, 2013 by using formulas, but just telling the inspector to display a cell with this number as a date just doesn't work.

With Excel set to the 1904 date basis, Numbers and Excel are using the same numeric algorithim to store a date as a number. Its just Excel lets you make use of it a LOT easier and to see the actual number, where as Numbers more or less hides the number and is a lot less flexible on displaying.

So Wayne, I don't understand your 'defense' of Numbers not being Excel. If the Inspector allows you to set the format of the cell display as currency, percentage, number, or date, you think that all those options would work or an error given. I'm not complaining that Numbers does not have a feature that Excel has, I am complaining that a feature of Numbers does not work very well at all.

Reply

Answer 3

Yellowbox

Level 7

21,939 points

Apr 24, 2013 11:10 PM in response to Paul Hillman

Hi Paul,

Interesting discussion and thank you for starting it. It is true that there are many threads on conversion between Numbers and Excel. I have this problem myself (with times, not dates) when working on a spreadsheet with a friend who uses Excel. More on that later, but I think I have found a way. It uses the number of minutes (a value) elapsed since 12 o'clock. Calculate then display that as a text value with hours:minutes.

Back to your discussion. Numbers has a Date and Time format. When you enter a date, it has a time attached to it (midnight on that day). Even though the cell does not display the time, it is still there. The same with times: they have a date attached even though it does not display.

Well done for discovering DATEVALUE and DATE as functions that work on both Numbers and Excel. I must give that a try.

You said:

I think this is a problem with Numbers.

We could equally say that is a problem with Excel! 😉

Regards,

Ian.

Reply

Answer 4

bquimby1

Level 1

4 points

Nov 24, 2013 5:05 AM in response to Paul Hillman

Dates and times are extremely important in many spreadsheets and you'd think that Numbers would convert Excel date serial numbers when you tell it that the cell is a date&time data type. In order to convert some of my spreadsheets to Numbers I'd have to manually compare the date cells and manually convert them and that's too time consuming to get me to switch.

Reply

Answer 5

Mar 19, 2014 5:54 PM in response to Paul Hillman

Hi All,

I'm just using Numbers for the first time and am having this same problem. I open an Excel sheet and the dates are changed to numbers that, to me at least, make no sense. For example, 3/2/2014 is changed to 40238. I can try all the different date format options and the number remains the same, never converting back to a date.

The initial post in this thread seemed to be sharing a fix or solution for this, but, it seems in this thread, you guys speak in a shorthand that goes above my head. I can't make sense of any solution that is being offered as I don't know what other cells to reference or why, and I need more detail about what is needed (are we supposed to put a new formula in each cell that has a date that is not showing correctly?). Can Paul or someone else give a clear, easy, step-by-step, explication of the way to convert these numbers back to dates, if that is indeed possible?

Thanks very much.

Tim

Reply

Answer 6

Hiroto

Level 5

7,467 points

Mar 20, 2014 3:47 AM in response to RidhwanTim

Hello

In order to get date in B1 from date serial number in A1, set the formula in B1 to:

=DATE(1904,1,1)+A1

In order to get date serial number in C1 from date in B1, set the formula in C1 to:

=DUR2DAYS(B1-DATE(1904,1,1))

So if you have date serial numbers in column A and want to replace them with corresponding dates, you may follow these steps:

1) Select and copy column A.

2) Paste it to column A of new temporary table.

3) Set the forumla in B1 of temporary table to =DATE(1904,1,1)+A1 and fill it down over column B (Insert > Fill > Fill Down [*]).

4) Select and copy column B of temporary table.

5) Paste its values to column A of original table (Edit > Paste Values [*]).

[*] Menu names are of Numbers v2 and may vary in Numbers v3.

Regards,

H

Reply

Answer 7

Mar 24, 2014 12:17 PM in response to Hiroto

Hi Hiroto,

Thanks for the response, and sorry for my delay. I only work on this project Monday thru Wednesdays.

I have tried your solution for the 3rd scenario above (date serial numbers in column A that I want to replace with corresponding dates). However, I run into two problems:

1. I am on version 3 of Numbers. Fill is no longer under the "Insert" menu. I can't find it on any of the menus. And a search on both the Help menu and Apple's Mac App Support page for Numbers for "fill down" reveals no understandable results. Most of the results seem be about changing appearances of cells. I'm a moderate-level Excel user, and I am unfamiliar with the "fill down" function. So I can only guess that this refers to Autofill. However, if I use Autofill I get the following problem.

2) First off: Before applying any formula, the numbers that were formerly dates in column A are not contiguous going down the rows. They are sometimes grouped together contiguously, but there are skipped rows to separate event dates. The formula you gave inserts dates on ALL the cells of column B, even if the adjacent cell in column A is empty. So it doesn't appear to be reading anything in column A cells with which to inform it's population of column B cells. Furthermore, it converts most the numbers that were formerly dates into the date 1/1/04 (though in some cases that date now also appears next to blank cells in column A, as just mentioned). The remaining dates look like they are in the correct ballpark (going back to the 80s), but they don't appear to be sensically connected to data in column A (also as mentioned above). I can find no rhyme or reason why, in some cases, blank cells are converted to right-seeming dates or 1/1/04 OR why in other cases cells with former dates are converted to right-seeming dates or 1/1/04. I've attached a screen shot to show what I'm referring to here.

Am I misunderstanding something or is there a better fix?

Thanks much,

Tim

Reply

Answer 8

Mar 24, 2014 1:37 PM in response to RidhwanTim

After some experimentation, I figured it out. I realized I wasn't starting my columns at A1 and had to change the formula to get it to work. When I did that, the blank cells in column A were the only cells in column B to be populated by 1/1/04 and the rest are populated with the correct date. I have to go in and delete the 1/1/04s, but I'm willing to do that. Which leads to a final question: Is there a way to find and delete all rather than find and replace all?

Thanks much!

Tim

Reply

Answer 9

Hiroto

Level 5

7,467 points

Mar 24, 2014 2:42 PM in response to RidhwanTim

Hello

You may use:

B1    =IF(ISBLANK(A1),"",DATE(1904,1,1)+A1)

instead of:

B1    =DATE(1904,1,1)+A1

and fill it down. This way, Bx will be blank if Ax is blank.

Regards,

H

Reply

Answer 10

Mar 24, 2014 2:59 PM in response to Hiroto

Many Thanks. I've already deleted them, but this is good to know for the future.

Reply

Answer 11

PaoloT.

Level 1

13 points

Nov 3, 2014 7:29 AM in response to Paul Hillman

Very interesting discussion. Unfortunately, not suggesting an easy solution to my problem.

I'm trying to convert all my spreadsheets from LibreOffice to Numbers. These sheets are laden with dates, that are imported by Numbers as rough numbers like '39835' or the like.

Copying and pasting all groups of dates converted with formulas is not realistic. I wonder if this is a missing feature that we can hope will be soon addressed, or it is so by design. If the latter is true, I wonder how could all those small business or personal users wishing to transfer all their personal finance to Numbers do the move.

Paolo

Reply