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

"=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

Like (0)


jakchris Indonesia
Dear Barry,
Thx for your reply.
I was referring to the cell range selection. 2716 rows. From top to down.
Indeed the Formula is as simple as it cabe.
What do mean by typo? I believe it's correct. I have the same formula in the next column and it show normal amounts.
I am working with Numbers since many years but this never occurred to me.
Rgds
Chris

Like (0)


"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

Like (0)


jakchris Indonesia
Barry;
It is in fact the same formula. The difference in column K it appears correctly in an amount.
Whereas in column L it appears in Date values.
formula in column K
Formula in column L
Result of the formula
The field in December58 is having the same problems.
Applied formatting settings; i have change these but it remains in this "Date"  December76
Interestingly if the data are changing so does the date change in.
Regards
Chris

Like (0)



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

Like (0)


jakchris Indonesia
Hi Jerry,
Welcome.
this is a file which i use in column A and B in category view.
We had moved several categories around it seems as some sorting was wrong.
It is updated by my accounting staff and i review it usually only.
Once i opened the file today I notice the date format. Thought this was a simple formatting fix.
Well i tried around the whole day.
What I do now is that I take a older file and ask my team to re update.
However it would be interesting to find out what this problem can be.
Regards
Chris

Like (0)


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

Like (0)


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

Like (0)


jakchris Indonesia
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

Like (0)


jakchris Indonesia
Dear Berry,
Appreciate your input. It was a typo on the first entry. Hence I thought best to include the screen shots.
LG should be in fact L6.
I whish you too a great a weekend Barry.
Best
Chris

Like (0)


 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 dateformatted 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

Like (0)

jakchris Indonesia
Hi Jerry,
After your comment i started digging again. I opened up the sorting tab and espanded all and started going through all after firstly formatting all once more.
Indeed there was a cell which contained a date entry as you discribed.
After deleting or setting it to "0" the formula turned correct. You were right.
Attached screen shot.
Before; Look at below left column;
After I changed the cell to 0
Excellent work Jerry;
Problem solved
Happy Sunday
Regards
Chris

Like (0)
