Skip navigation

Why my number formatting in Numbers turns into a date format?

666 Views 13 Replies Latest reply: Jan 19, 2013 9:12 PM by jakchris RSS
jakchris Level 1 Level 1 (0 points)
Currently Being Moderated
Jan 17, 2013 8:16 PM

Dear Forum,

 

Can somebody explain why my numbers format turns into a date format like "October-41"

Cell Format - Number - No Decimal.

I have a formula with following details

=SUM(LG:L2716)


It is a long formula but in the past i never had any problems with it

MacBook Pro, OS X Mountain Lion (10.8.2), 17"
  • Barry Level 7 Level 7 (29,095 points)

    "=SUM(LG:L2716)"

     

    Typo? I get "LG:L2716 is not a valid reference"

     

    You say you have a "long formula." As the example is close to as short as a Numbers formula gets (one function), I have to ask, "What is your 'long formula'?"

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)

    "What do mean by typo? I believe it's correct. I have the same formula in the next column and it show normal amounts."

     

    typo = typographical error = typed the wrong character

     

    What is the formula in the next column? How is it different from the formula you presented in your initial post?

     

    Regards,

    Barry

  • Jerrold Green1 Level 7 Level 7 (28,160 points)

    Chris,

     

    Do you remember what you were doing to the document at the time it started acting badly?

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,160 points)

    Chris,

     

    I don't think we can guess, from the small bits of information we can see in your posts, what the problem may have been. Starting again with a good copy of the document is the best way forward.

     

    The most common reason for a Numeric value being represented as a Date value is that there is a character in the cell content that doesn't belong in a Numeric value. A hyphen or slash can cause this. I've noticed that once a content is interpreted regarding it's data type, it can be difficult to get the format to revert without clearing out the data and starting over with it.

     

    To avoid uncomfortable situations in the future, make sure you have a duplicate backup copy when you do any extensive reformatting. I'm glad you found a way to recover.

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)

    HI Chris,

     

    I think Jerry has hit on a plausible explanation of Numbers misinterpreting some numbers as Date and Time values, and like him.

     

    Regarding my initial question: Your formula, as presented in your original post is significantly different from the two shown in the later screen shots:

     

    "LG" in the first is not a valid cell reference,

    "L6" in the screen shot is a valid cell reference.

     

    I'm glad that you've found a way to recover the document.

     

    Regards,

    Barry

  • Jerrold Green1 Level 7 Level 7 (28,160 points)
    • jakchris wrote:

     

    Hi Jerry,

     

    I have found a similar entry on one cell. It was a slash, however after i removed it didn't change.

    I would need to go through all the whole file and check each row which will take quite some time.

    So as you suggest to have a constant back is the best take on it.

    Since I do have daily backups from my system this should be no big problems.

     

    Many thanks for your input Jerry.

     

    Have a great weekend

     

    Chris

    Chris,

     

    I just received an email from a follower of these forums who did some digging into the problem and was able to characterize the behavior you are seeing. It seems that it just takes one date-formatted entry in a SUM range to convert the SUM result to a date, as long as the other values are either Numeric values or Durations. If there are two date values, SUM will return an error because dates are not allowed to be additive.

     

    If you were to write: =SUM(1 April 2013, 10) you would get 11 April 2013.

     

    If you were to write:  =SUM(1 April 2013, 1w, 1) you would get 9 April 2013.

     

    In the last example you have the SUM of a Date value, a Duration value and a Numeric value. The Numeric value defaults to Days, the natural unit of Dates.

     

    Regards,

     

    Jerry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.