Hello Everybody,
This is regarding the above mentioned error message. Working on SharePoint from last few years, i had seen customers used the date functions on regular basis inside their SharePoint list and libraries.
Depending the same, i worked on similar case which was weird in the start but finally the issue has been resolved with some perfect break-fix.
Let me describe the issue first:
-User had created one custom list.
-Created three columns: two were custom and one is calculated.
Custom Columns: Date1 and Date2 with content type Date and Time.
Calculated Column:CalcDate with content type calculated.
-User wanted to use basic date functions as difference between two dates, TEXT Function,DATE(year,month,day) etc...
-Custom columns has been created without any issues but when he were using a formula inside calculated column then he got the following error message:
This is regarding the above mentioned error message. Working on SharePoint from last few years, i had seen customers used the date functions on regular basis inside their SharePoint list and libraries.
Depending the same, i worked on similar case which was weird in the start but finally the issue has been resolved with some perfect break-fix.
Let me describe the issue first:
-User had created one custom list.
-Created three columns: two were custom and one is calculated.
Custom Columns: Date1 and Date2 with content type Date and Time.
Calculated Column:CalcDate with content type calculated.
-User wanted to use basic date functions as difference between two dates, TEXT Function,DATE(year,month,day) etc...
-Custom columns has been created without any issues but when he were using a formula inside calculated column then he got the following error message:
Troubleshooting done:
-Created a same list on my personal mysite and used the same functions, it worked without any problems...
-Compared features between my site and user's site. Found only difference i.e. publishing feature was activated on my site but wait wait....this is of no use because after deactivating the publishing feature also, i can use the same date functions.
-We have site collections created under so many domains, so i had used a site collection which belongs to different domain & still i can use the date functions without any issues...
-I had used different site collection which belongs to same domain which is used by user also, and here also i can use without any problems...
Workaround:
-Save the SharePoint list as template on which we can use the date functions without any problems.
Next step is very simple, go to the affected site collection list gallery and upload ( please refer the following steps):
-open the site collection
-site actions
-site settings
-Galleries
-List Templates
Resolution:
You just have to use ";" instead of "," it works fine without any issues..
Examples:
+Calculate the difference between two dates:
=DATEDIF([Column1]; [Column2];"d")
Please refer the below mentioned Microsoft published article which is excellent as it contains a basic functions that we can use in SharePoint.
http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA010105479.aspx?CTT=3
If you face any queries /questions regarding the above mentioned procedure/information then please let me know...
I would be more than happy to help you as well as resolves your issues. Thank you very much once again :-)
Your blog is really amazing!
ReplyDeletethanks,this wrok for me
ReplyDeleteI have a list with a column where data is entered as month/year (IE: DECEMBER 2011 and so forth). I need to be able to create a view that will only display the current months data automatically. I am currently creating a new view when each month starts.
ReplyDeleteCan you help? I am using SharePoint 2007.
Please refer the following link: http://blogs.msdn.com/b/sharepointdesigner/archive/2008/08/01/date-functions-in-calculated-fields.aspx
ReplyDeletei think the above mentioned link will fulfilled your requirements, in case of any questions then please let me know, Thank you
It worked perfectly using an actual date. I was hoping I could just use the Month and Year such as December 2012 but that isn't an a true date. I will keep plugging along. Thank you for your help!
ReplyDeleteI think this is one of the most important information for me.
ReplyDeleteAnd i'm glad reading your article. But want to remark on few general things, The site style is great, the articles is really nice : D. Good job, cheers
Take a look at my website : default
is there any way my list accepts this formula:
ReplyDelete=IF([Action Status]="Closed","",IF([Action Status]="Escalated to APM",[Last ILC Action Date]+7,IF([Action Status]="Monitoring",[Last ILC Action Date]+2,IF(ISBLANK([Target Close Date])[Last ILC Action Date]+2,IF(([Last ILC Action Date]+2)<([Target Close Date]-1),[Target Close Date]-1,[Last ILC Action Date]+2)))))
it works perfectly in an excel sheet bat when i input the same in the Formula field it says syntax error.
I hope you have already followed the above mentioned recommendations.
ReplyDeleteCan you please tell me the purpose of this formula so that i can try to replicate the same at my end?
Hi Amol,
ReplyDeletethis was just the solution I was looking after struggling with syntax errors.
Replace , by ;!
I could not never have found it myself.
Thank you.
yup worked for me too. thnx!!
ReplyDeleteAwesome, Thanks for the confirmation!
Delete