Sharepoint formula problem

Question

Hello Everyone,&nbsp;As I am trying to add calculated column it is showing error.&nbsp;For going in details please find attachments.&nbsp;Regards,Sagar Shah

racrig · Answer

Shah1827&nbsp;I found some old notes where I did something like that - calculating the difference between two date fields, down to the minutes. The users wanted to know how much time passed between&nbsp;task creation and the time it was marked as Started. Also how much time passed between the time it was marked Started and the time it was marked Completed. If you don't need minutes, just delete that part from the formula.&nbsp;Created to Started
	=CONCATENATE(INT([Start date]-Created)," Days ",TEXT([Start date]-Created,"h"" hrs ""m"" mins """))

Started to Completed
	=CONCATENATE(INT([Completed Date]-[Start Date])," Days ",TEXT([Completed Date]-[Start Date],"h"" hrs ""m"" mins """))

Created to Completed
=CONCATENATE(INT([Completed Date]-Created)," Days ",TEXT([Completed Date]-Created,"h"" hrs ""m"" mins """))&nbsp;&nbsp;These formulas were put into Calculated columns. Output must be text. The fields they are referencing are all DATE fields. The "Created" field is the system field that SharePoint creates automatically, but the others&nbsp;were all custom date fields.&nbsp;The results were:&nbsp;&nbsp;I also found this old note about date differences, but I'm not sure what it will do. Looks like it will just return the number of days between two dates.&nbsp;Formula:
=DATEDIF([Start Date],[Due Date],"D")

Settings:
select&nbsp;Number&nbsp;for the data type returned from the formula, select&nbsp;0&nbsp;for the number of decimal places, and click&nbsp;OK​&nbsp;&nbsp;

steveknutson · Answer

Shah1827&nbsp;the error indicates that you can't see the default value based on another column. That's because your column type is single line of text. You need to use a Calculated Column instead.
&nbsp;
&nbsp;

shah1827 · Answer

But for date column how can we use calculated column.

racrig · Answer

Shah1827&nbsp;Not sure what you are trying to calculate, but here are some references for sharepoint calculated columns:https://support.microsoft.com/en-us/office/examples-of-common-formulas-in-sharepoint-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3&nbsp;&nbsp;https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/bb862071(v=office.14)&nbsp;&nbsp;https://natechamberlain.com/2018/08/27/extract-date-components-using-the-text-function-in-sharepoint-calculated-columns/&nbsp;&nbsp;But yes, your column must be of type "Calculated" if you want a value derived from a formula. It can't be Single Line of Text.

shah1827 · Answer

Hello&nbsp; ,&nbsp;Thanks for your reply as I am trying to generate report of daily task , so I am trying to calculate how many hours Employee has done work. like start date and timing to end date and timing.&nbsp;

Forum Discussion

Sharepoint formula problem

6 Replies

Resources