Forum Discussion

Shah1827's avatar
Shah1827
Copper Contributor
Jul 03, 2020

Sharepoint formula problem

Hello Everyone,   As I am trying to add calculated column it is showing error.   For going in details please find attachments.   Regards, Sagar Shah
SharePoint Online
SteveKnutson's avatar
SteveKnutson
MVP
Jul 03, 2020

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.

 

 

Shah1827's avatar
Shah1827
Copper Contributor
Jul 06, 2020
But for date column how can we use calculated column.
  • Shah1827's avatar
    Shah1827
    Copper Contributor
    Jul 07, 2020

    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. 

    • racrig's avatar
      racrig
      Brass Contributor
      Jul 07, 2020

      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:

       

       

      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​

       

       

      • Jeppe_Nygaard's avatar
        Jeppe_Nygaard
        Copper Contributor
        May 25, 2022
        * Output must be text.
        ahh thats the reason !!! THaNK YOU

Resources