Reusing old formula gives me syntax error in Sharepoint online

%3CLINGO-SUB%20id%3D%22lingo-sub-799986%22%20slang%3D%22en-US%22%3EReusing%20old%20formula%20gives%20me%20syntax%20error%20in%20Sharepoint%20online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799986%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20I%20Am%20new%20to%20this%20site%20and%20stumbled%20upon%20it%20while%20trying%20to%20find%20a%20solution%20for%20the%20problem%20I%20currently%20have.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20about%20to%20transfer%20content%20from%20older%20SharePoint%20site%20to%20SharePoint%20365%20(online).%3CBR%20%2F%3EIn%20the%20older%20site%20we%20used%20many%20different%20formulas%20to%20set%20up%20many%20of%20our%20lists.%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20in%20the%20online%20version%20all%20of%20the%20formulas%20gives%20me%20syntax%20errors.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EExample%201%3A%3C%2FU%3E%3C%2FP%3E%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3Ethis%20formula%20is%20used%20in%20a%20handover%20list%2C%20where%20its%20supposed%20to%20make%20it%20possible%20to%20sort%20all%20items%20accordingly%20to%20week%20number.%3CBR%20%2F%3E%3CU%3ECODE%3A%3C%2FU%3E%3CBR%20%2F%3E%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DYEAR(Created)%26amp%3B%22%20%3A%20%22%26amp%3BIF(INT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3)%0A%2BWEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3))%2B5)%2F7)%26lt%3B10%3B%220%22%26amp%3BINT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3)%0A%2BWEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3))%2B5)%2F7)%3BINT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3)%0A%2BWEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3))%2B5)%2F7))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3E%3CU%3EExample%202%3C%2FU%3E%3CBR%20%2F%3EThis%20is%20a%20list%20that%20have%20been%20made%20in%20to%20a%20journal.%20In%20the%20old%20journal%20we%20have%20columns%20that%20sort%20from%20year%20and%20month.%20Type%20of%20column%20is%20%22calculated%20(calculated%20based%20on%20other%20columns).%20In%20the%20old%20we%20used%20this%20code%3A%3CBR%20%2F%3EYear%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXT(Date%3B%22yyyy%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3EMonth%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DTEXT(Date%3B%22MMMM%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CBR%20%2F%3ENot%20any%20of%20the%20formulas%20that%20I%20have%20used%20in%20the%20older%20version%20works%20in%20SharePoint%20online.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3Ewhy%20is%20that%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3Ethis%20formula%20is%20used%20in%20a%20handover%20list%2C%20where%20its%20supposed%20to%20make%20it%20possible%20to%20sort%20all%20items%20accordingly%20to%20week%20number.CODE%3A%3DYEAR(Created)%26amp%3B%22%20%3A%20%22%26amp%3BIF(INT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3)%0A%2BWEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3))%2B5)%2F7)%26lt%3B10%3B%220%22%26amp%3BINT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3)%0A%2BWEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3))%2B5)%2F7)%3BINT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3)%0A%2BWEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%3B1%3B3))%2B5)%2F7))Example%202This%20is%20a%20list%20that%20have%20been%20made%20in%20to%20a%20journal.%20In%20the%20old%20journal%20we%20have%20columns%20that%20sort%20from%20year%20and%20month.%20Type%20of%20column%20is%20%22calculated%20(calculated%20based%20on%20other%20columns).%20In%20the%20old%20we%20used%20this%20code%3AYear%3A%3DTEXT(Date%3B%22yyyy%22)Month%3A%3DTEXT(Date%3B%22MMMM%22)Not%20any%20of%20the%20formulas%20that%20I%20have%20used%20in%20the%20older%20version%20works%20in%20SharePoint%20online.%26nbsp%3Bwhy%20is%20that%3F%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-799986%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2013%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3E2016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDocument%20Library%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESites%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-803729%22%20slang%3D%22en-US%22%3ERe%3A%20Reusing%20old%20formula%20gives%20me%20syntax%20error%20in%20Sharepoint%20online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803729%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390886%22%20target%3D%22_blank%22%3E%40mortenf%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20code%20from%20example%20%232%20should%20work.%20Using%20the%20%22Created%22%20column%20the%20code%20works%20in%20my%20SPO%20test%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DTEXT(Created%2C%22yyyy%22)%3CBR%20%2F%3E%3CBR%20%2F%3E%3DTEXT(Created%2C%22MMMM%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20in%20example%20%231%20is%20more%20complicated%20and%20harder%20to%20debug.%20If%20you%20are%20looking%20for%20week%20of%20year%20this%20formula%20will%20work%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DINT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%2C1%2C3)%2BWEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)%2B4)%2C1%2C3))%2B5)%2F7)%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126888i9FFCE503B9C39190%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20hope%20this%20helps.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ENorm%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi.

 

First of all, I Am new to this site and stumbled upon it while trying to find a solution for the problem I currently have. 

 

I am about to transfer content from older SharePoint site to SharePoint 365 (online).
In the older site we used many different formulas to set up many of our lists. 

But in the online version all of the formulas gives me syntax errors.

 

Example 1:

Spoiler
this formula is used in a handover list, where its supposed to make it possible to sort all items accordingly to week number.
CODE:
Spoiler
=YEAR(Created)&" : "&IF(INT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)+4);1;3)
+WEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)+4);1;3))+5)/7)<10;"0"&INT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)+4);1;3)
+WEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)+4);1;3))+5)/7);INT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)+4);1;3)
+WEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)+4);1;3))+5)/7))


Example 2
This is a list that have been made in to a journal. In the old journal we have columns that sort from year and month. Type of column is "calculated (calculated based on other columns). In the old we used this code:
Year:

=TEXT(Date;"yyyy")


Month:

=TEXT(Date;"MMMM")


Not any of the formulas that I have used in the older version works in SharePoint online. 

why is that?


 

 

1 Reply
Highlighted

Hello @mortenf,

 

Your code from example #2 should work. Using the "Created" column the code works in my SPO test list.

 

=TEXT(Created,"yyyy")

=TEXT(Created,"MMMM")

 

The code in example #1 is more complicated and harder to debug. If you are looking for week of year this formula will work:

 

=INT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)+4),1,3)+WEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)+4),1,3))+5)/7)

 

Capture.PNG

 

I hope this helps.

 

Norm