Syntax error in Numbers spreadsheet.

I have a Numbers spreadsheet that was imported from an excel spreadsheet that comes up with this error message “The formula contains a syntax error”. 




the formula is to take the date from column B Row Number and insert that date into the Transaction sheet on the same line as the column that has the date you want to insert in the transaction sheet. 




The Transaction sheet has tow lines instead of one line for each entry. It is a financial register and transaction record  spreadsheet. The difference between the excel sheet and the numbers sheet is, excel has both sheets on the same page and numbers has it on two pages. With that being on different pages, you can’t just click on the different cells to insert a formula because when you go to t he second sheet the edit formula disappears.




So the formula that comes up on my transaction sheet is: 


IF(Register::Table 1::B9<1÷1÷16,"-",Register::Table 1::B9)


The table line changes for each entry. 




Now my question is: How can I change the formula so I get the date from the Register sheet to enter the date into my Transaction sheet. 




This spreadsheet was designed at our national organization for use by the Treasurers for our chapters accounting of chapters funds. 


I am going to try and put a photo of the spreadsheet and the Transaction sheet. You can see the error in the Transaction Register photo.



iMac 21.5″, macOS 10.13

Posted on Feb 23, 2022 9:39 AM

Reply
Question marked as Top-ranking reply

Posted on Feb 23, 2022 4:12 PM

Numbers and Excel handle dates differently. Excel uses a number. Numbers uses a special format of Date&Time. Both can do math with a date formatted as text.


Was that formula from the import from Excel or did you create it in Numbers? It looks odd to be a direct import from Excel but it does not appear to have a syntax error. If B9 contains a date, it should be giving the error that you cannot compare a date with a number. If 1÷1÷16 was supposed to be the date 1/1/16, put it in as "1/1/2016" (with the quotes) or as DATE(2016,1,1).


=IF(Register::Table 1::B9<"1/1/2016","-",Register::Table 1::B9)

=IF(Register::Table 1::B9<DATE(2016,1,1),"-",Register::Table 1::B9)


See if that helps.

Similar questions

1 reply
Question marked as Top-ranking reply

Feb 23, 2022 4:12 PM in response to Goldwinggene

Numbers and Excel handle dates differently. Excel uses a number. Numbers uses a special format of Date&Time. Both can do math with a date formatted as text.


Was that formula from the import from Excel or did you create it in Numbers? It looks odd to be a direct import from Excel but it does not appear to have a syntax error. If B9 contains a date, it should be giving the error that you cannot compare a date with a number. If 1÷1÷16 was supposed to be the date 1/1/16, put it in as "1/1/2016" (with the quotes) or as DATE(2016,1,1).


=IF(Register::Table 1::B9<"1/1/2016","-",Register::Table 1::B9)

=IF(Register::Table 1::B9<DATE(2016,1,1),"-",Register::Table 1::B9)


See if that helps.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Syntax error in Numbers spreadsheet.

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