Sharepoint formula problem

%3CLINGO-SUB%20id%3D%22lingo-sub-1503626%22%20slang%3D%22en-US%22%3ESharepoint%20formula%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1503626%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20am%20trying%20to%20add%20calculated%20column%20it%20is%20showing%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20going%20in%20details%20please%20find%20attachments.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ESagar%20Shah%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1503626%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

Hello Everyone,

 

As I am trying to add calculated column it is showing error.

 

For going in details please find attachments.

 

Regards,

Sagar Shah

5 Replies
Highlighted

@Shah1827 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.

 

 

Highlighted
But for date column how can we use calculated column.
Highlighted

@Shah1827 Not sure what you are trying to calculate, but here are some references for sharepoint calculated columns:

Examples of common formulas 

 

Calculated Field Formulas 

 

Extract date components using TEXT() 

 

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.

Highlighted

Hello  ,

 

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. 

Highlighted

@Shah1827 

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 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.

 

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 """))

 

 

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 were all custom date fields. The results were:

 

datecalc.png

 

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.

 

Formula:
=DATEDIF([Start Date],[Due Date],"D")

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