SOLVED

IF formula error message "The formula contains a syntax error."

%3CLINGO-SUB%20id%3D%22lingo-sub-3372833%22%20slang%3D%22en-US%22%3EIF%20formula%20error%20message%20%22The%20formula%20contains%20a%20syntax%20error.%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3372833%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20a%20very%20simple%20IF%20formula%20in%20a%20calculated%20column%20at%20my%20data%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%5BMonitorar%5D%3D0%3B%220%22%3B%221%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20replacing%26nbsp%3B%20the%20%3B%20by%20%2C%20and%20not%20solved.%3C%2FP%3E%3CP%3EI%20also%20tried%20replacing%20IF%20by%20SE%20(note%20that%20my%20Microsoft%20account%20is%20in%20portuguese)%2C%20and%20not%20solved.%3C%2FP%3E%3CP%3EI%20tried%20in%20another%20browsers%2C%20an%20nothing%20different%20happens.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Marketing-Controle-de-Altera%C3%A7%C3%B5es-Google-Ads-Todos-os-Itens.png%22%20style%3D%22width%3A%20861px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371542i979A4584F47EF693%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Marketing-Controle-de-Altera%C3%A7%C3%B5es-Google-Ads-Todos-os-Itens.png%22%20alt%3D%22Marketing-Controle-de-Altera%C3%A7%C3%B5es-Google-Ads-Todos-os-Itens.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3372833%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECalculated%20Column%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Econditions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EIF%20Formula%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376670%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%20error%20message%20%22The%20formula%20contains%20a%20syntax%20error.%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376670%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIF(%5BMonitorar%5D%3D0%2C%200%2C1)%3CBR%20%2F%3E%3CBR%20%2F%3ETry%20using%20commas%20and%20remove%20the%20quotes.%20I%20think%20a%20quotes%20are%20for%20text%2C%20and%20you%20can%E2%80%99t%20pass%20text%20into%20a%20number%20column.%20Let%20me%20know%20if%20that%20works.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3379557%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%20error%20message%20%22The%20formula%20contains%20a%20syntax%20error.%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3379557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1362701%22%20target%3D%22_blank%22%3E%40valderes-squadra%3C%2FA%3EFrom%20the%20screenshot%20you%20attached%2C%20it%20looks%20like%20%5BMonitorar%5D%20column%20is%20of%20type%20%22Date%22%20and%20not%20%22Number%22.%20Is%20this%20causing%20the%20problem%20here%3F%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%20%26amp%3B%20%3CSTRONG%3ELike%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3382479%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%20error%20message%20%22The%20formula%20contains%20a%20syntax%20error.%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3382479%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20column%20Monitorar%20is%20a%20date%2Ftime%20field%2C%20but%20the%20calculated%20column%20is%20a%20number%20field%20and%20should%20accept%20either%200%20or%201.%26nbsp%3B%20I%20wasn't%20sure%20that%20a%20date%20could%20equal%200%2C%20but%20I%20tested%20this%20and%20it%20works%20fine%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(%5BDue%20Date%5D%3D0%2C0%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%26nbsp%3B%20today%20I%20learned%20a%20empty%20date%20field%20equals%200.%26nbsp%3B%20Good%20stuff!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3398950%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20formula%20error%20message%20%22The%20formula%20contains%20a%20syntax%20error.%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3398950%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20to%20all%20for%20your%20replies.%20Obviousl%C3%BD%20I%20forgot%20that%20numbers%20don't%20need%20quotes.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1195800%22%20target%3D%22_blank%22%3E%40pamjam%3C%2FA%3E%2C%20your%20solution%20worked.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all.

 

I'm trying a very simple IF formula in a calculated column at my data list.

 

=IF([Monitorar]=0;"0";"1")

 

I tried replacing  the ; by , and not solved.

I also tried replacing IF by SE (note that my Microsoft account is in portuguese), and not solved.

I tried in another browsers, an nothing different happens.

 

 

Marketing-Controle-de-Alterações-Google-Ads-Todos-os-Itens.png

4 Replies

=IF([Monitorar]=0, 0,1)

Try using commas and remove the quotes. I think a quotes are for text, and you can’t pass text into a number column. Let me know if that works.

@valderes-squadraFrom the screenshot you attached, it looks like [Monitorar] column is of type "Date" and not "Number". Is this causing the problem here?


Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

best response confirmed by valderes-squadra (Occasional Contributor)
Solution

The column Monitorar is a date/time field, but the calculated column is a number field and should accept either 0 or 1.  I wasn't sure that a date could equal 0, but I tested this and it works fine:

 

=IF([Due Date]=0,0,1)

 

So  today I learned a empty date field equals 0.  Good stuff!

Thanks to all for your replies. Obviouslý I forgot that numbers don't need quotes. @PamDeGraffenreid, your solution worked.