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

Numbers problem

Hi, I am fairly new to Numbers and have started a spreadsheet. In column A I want dates, with each incrementing by one. When I try to enter this, I get the read triangle with "The operator “+” expects a number, date, or duration, but cell A4 contains a string.". Cell A4 is a date but when I click on it it has reverted back to automatic. How can I rectify this? TIA


Macbook Pro 2015, Processor 2,2 GHz Intel Core i7, Memory 16GB, Storage 250,14 GB

OSX Yosemite 10.10.5, Numbers 3.5

Posted on May 2, 2016 9:16 AM

4 replies

May 2, 2016 2:22 PM in response to Community User

Hi dc,


If cell A4 contains a Date & Time value, and your formula in A5 is =A4+1, then your result in A5 will be the Date & Time value for the next day.


When a date is entered into a cell whose format is set to Automatic, Numbers will, if the date is in a format recognized as the date part of a Date & Time value, add the missing bits to make it a full Date & Time, then display only the part that has been entered, matching the style used to enter it. If text alignment in the cell has been set to Automatic, the result will be aligned to the right edge of the cell.


If Numbers does not recognize the entry as a date, it will be accepted as text, and if alignment is Automatic, will be aligned to the left edge of the cell.


Setting the cell format to Date & Time sets only the way that Date & Time values entered in that cell will be displayed. If something other than a date or a time or a full date and time value has been entered, the format setting, not able to recognize it, will revert to Automatic, and provide the most appropriate formatting.


The error message indicates that your "Date" was not recognized as a date, and was accepted as text. Here's an example showing entries of "dec 5" Sept 3 and December 1 (not yet entered):

User uploaded file

In F2, Numbers has recognized dec 5 as a date, completed the date part with the current year, and set the time part to 00:00:00, the beginning of that day, has displayed the result according to the Data format set in the format Inspector, and aligned it right (as a numeric or number-like value).


In F3, Numbers has not recognized "Sept 3" as a date, but has accepted it as text, and aligned it left.


F4 when entered, will be treated in the same manner as F2, as it is in a format recognized by Numbers as a Date.


Try leaving the format setting for A4 as Automatic and setting the alignment to Automatic as well, then re-enering the date. If it aligns right, Numbers has recognized the format, and the formula should not produce that error message.


Regards,

Barry

May 3, 2016 8:25 AM in response to Community User

The interesting thing is that Numbers is quite forgiving. You can enter a date as true text (e.g., you could type an ' first so Numbers treats what you enter as text and thus doesn't recognize it as a date-time string that is aligned to the right in the cell) and you can still increment the "date" by simply adding 1 to it.


A2 here is clearly text:


User uploaded file



But when you add 1 to it Numbers realizes that, although the cell is formatted as Text, the contents can be interpreted as a date and thus treats the result as Automatic/Date & Time.


User uploaded file



The trick is to make sure you enter the date (either as true date-time or as text) in a format that Numbers detects can be interpreted as a date. Usually that will be in one of the formats you see in your dropdown in the format panel at the right (mine looks like this):


User uploaded file


So if you're getting a red triangle make sure you're entering in one of the formats that your setup can recognize as a date. That can depend on your settings in System Preferences > Language & Region.


SG

Numbers problem

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