How to created calculated Column

%3CLINGO-SUB%20id%3D%22lingo-sub-1083673%22%20slang%3D%22en-US%22%3EHow%20to%20created%20calculated%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1083673%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20sorry%20this%20is%20very%20beginner%20and%20i've%20tried%20to%20look%20up%20some%20syntax%20examples%20but%20i%20can't%20get%20the%20formula%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20a%20list%20that%20has%20a%20date%20column.%20I%20want%20to%20create%20a%20calculated%20column%20that%20takes%20that%20date%20and%20displays%20it%20as%20a%20string%20type%20(for%20filtering%20purposes)%20so%20i%20THINK%20the%20formula%20i%20want%20is%20something%20like%3C%2FP%3E%3CP%3E%22%3DText(%5BWE%20Date%5D)%22%20but%20i%20know%20that's%20not%20exactly%20correct.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1083673%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELists%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-1083704%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20created%20calculated%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1083704%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F287320%22%20target%3D%22_blank%22%3E%40ellan1537%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20done%20that%20several%20times%2C%20i'm%20so%20confused.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20582px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F163392i484D1D437C80B29F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EYou%20can%20see%20i'm%20creating%20a%20column%20named%20dateString.%20I've%20selected%20calculated.%20the%20formula%20i%20am%20using%20is%20%22%3D%5BWE%20Date%5D%22%20and%20im%20returning%20a%20single%20line%20of%20text.%20I%20press%20okay%20and%20sharepoint%20gives%20me%20an%20error.%20It%20does%20not%20create%20the%20column.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1083714%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20created%20calculated%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1083714%22%20slang%3D%22en-US%22%3ETry%20this%20syntax.%20It%20worked%20for%20me.%3CBR%20%2F%3E%3DTEXT(%5BColumn_Name%5D%2C%22DD%2FMM%2FYYYY%20hh%3Amm%22)%3CBR%20%2F%3E%3CBR%20%2F%3EEg%3A%20%3DTEXT(%5BStart%20Date%2FTime%5D%2C%22DD%2FMM%2FYYYY%20hh%3Amm%22)%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20syntax%20will%20display%20the%20time%20in%2024%20hour%20format.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1083715%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20created%20calculated%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1083715%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F287320%22%20target%3D%22_blank%22%3E%40ellan1537%3C%2FA%3E%26nbsp%3BI%20just%20realized%2C%20I%20think%20It's%20because%20i%20have%2037%2C000%20items%20in%20this%20sharepoint%20list%20and%20therefore%20exceeds%20the%20list%20view%20threshold....%20but%20i%20don't%20ever%20actually%20VIEW%20this%20list%3F%3F%20i%20use%20it%20as%20a%20data%20source%20for%20powerapps.%3C%2FP%3E%3CP%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%2F163393i3E27268E87E8BC0F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1083691%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20created%20calculated%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1083691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F502146%22%20target%3D%22_blank%22%3E%40Kyle_Johnston%3C%2FA%3E%26nbsp%3BFollow%20below%20steps%3A%3C%2FP%3E%3CP%3E1.%20Create%20a%20column%20(say%20START%20DATE%2FTIME)%20with%20column%20type%20'Date%20and%20Time'.%3C%2FP%3E%3CP%3E2.%20Now%20I%20want%20to%20convert%26nbsp%3BSTART%20DATE%2FTIME%20column%20to%20datatype%20'string'.%20So%2C%20create%20a%20new%20column%20with%20type%20CALCULATED.%3C%2FP%3E%3CP%3E3.%20In%20'Additional%20Column%20Settings'%2C%20use%20the%20formula%20%3CSPAN%3E%3DTEXT(%5BStart%20Date%2FTime%5D%2C%22DD%2FMM%2FYYYY%20hh%3Amm%22)%26nbsp%3B%3C%2FSPAN%3Eand%20select%20the%20data%20type%20returned%20as%20SINGLE%20LINE%20OF%20TEXT%20(syntax%3A%20%3CSPAN%3E%3DTEXT(%5BColumn_Name%5D%2C%22DD%2FMM%2FYYYY%20hh%3Amm%22)%3C%2FSPAN%3E).%26nbsp%3B%3CSPAN%3EThis%20syntax%20will%20display%20the%20time%20in%2024%20hour%20format.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E4.%20Click%20OK.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20this%20field%20will%20hold%20the%20given%20data%2Ftime%20input%20in%20string%20format.%20You%20can%20hide%20this%20column%20from%20users%20and%20can%20apply%20filtering%2Fsorting%20to%20display%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIt%20worked%20for%20me.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22comic%20sans%20ms%2Csans-serif%22%20size%3D%222%22%3E%3CEM%3ELast%20Edit%3A%2012%2F30%2F2019%202%3A01%20PM%20CST%26nbsp%3B%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'm sorry this is very beginner and i've tried to look up some syntax examples but i can't get the formula right.

 

i have a list that has a date column. I want to create a calculated column that takes that date and displays it as a string type (for filtering purposes) so i THINK the formula i want is something like

"=Text([WE Date])" but i know that's not exactly correct. 

4 Replies
Highlighted

@Kyle_Johnston Follow below steps:

1. Create a column (say START DATE/TIME) with column type 'Date and Time'.

2. Now I want to convert START DATE/TIME column to datatype 'string'. So, create a new column with type CALCULATED.

3. In 'Additional Column Settings', use the formula =TEXT([Start Date/Time],"DD/MM/YYYY hh:mm") and select the data type returned as SINGLE LINE OF TEXT (syntax: =TEXT([Column_Name],"DD/MM/YYYY hh:mm")). This syntax will display the time in 24 hour format.

4. Click OK.

 

Now, this field will hold the given data/time input in string format. You can hide this column from users and can apply filtering/sorting to display data.

 

Hope this helps!

 

It worked for me.

 

Last Edit: 12/30/2019 2:01 PM CST 

Highlighted

@ellan1537 

 

I've done that several times, i'm so confused. 

image.png

You can see i'm creating a column named dateString. I've selected calculated. the formula i am using is "=[WE Date]" and im returning a single line of text. I press okay and sharepoint gives me an error. It does not create the column. 

Highlighted
Try this syntax. It worked for me.
=TEXT([Column_Name],"DD/MM/YYYY hh:mm")

Eg: =TEXT([Start Date/Time],"DD/MM/YYYY hh:mm")

This syntax will display the time in 24 hour format.
Highlighted

@ellan1537 I just realized, I think It's because i have 37,000 items in this sharepoint list and therefore exceeds the list view threshold.... but i don't ever actually VIEW this list?? i use it as a data source for powerapps.

clipboard_image_0.png