Calculate Time difference in calculated column

%3CLINGO-SUB%20id%3D%22lingo-sub-1905787%22%20slang%3D%22en-US%22%3ECalculate%20Time%20difference%20in%20calculated%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1905787%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20do%20i%20calculate%20the%20time%20difference%20between%20time%20delivered%20and%20time%20accepted.%20Both%20of%20the%20columns%20are%20single%20line%20of%20text.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20time%20taken%20column%20is%20a%20calculated%20single%20line%20of%20text%20type.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AloisOhZH_0-1605767018417.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F234639iF273404DD0BC046B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AloisOhZH_0-1605767018417.png%22%20alt%3D%22AloisOhZH_0-1605767018417.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3EAlois%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1905787%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1905839%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20Time%20difference%20in%20calculated%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1905839%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F825020%22%20target%3D%22_blank%22%3E%40AloisOhZH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20share%20the%20formula%20you%20are%20currently%20using%20for%20%22Time%20Taken%22%3F%3C%2FP%3E%3CP%3EThat%20will%20help%20us%20to%20identify%20the%20issue%20with%20formula%20and%20answer%20your%20question%20more%20precisely.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20why%20are%20you%20using%20single%20line%20of%20text%20fields%20for%20Time%20Accepted%20and%20Time%20Delivered%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGeneral%20format%20for%20your%20formula%20should%20something%20be%20like%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%3DTEXT(DATE(%5BColumn2%5D)-DATE(%5BColumn1%5D)%2C%22h%3Amm%3Ass%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSTRONG%3EReference%3C%2FSTRONG%3E%3A%26nbsp%3B%3CA%20title%3D%22Date%20and%20time%20formulas%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fdeveloper%2Fsharepoint-2010%2Fbb862071(v%3Doffice.14)%23date-and-time-formulas%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EDate%20and%20time%20formulas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1916682%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20Time%20difference%20in%20calculated%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1916682%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373269%22%20target%3D%22_blank%22%3E%40ganeshsanap%3C%2FA%3E%26nbsp%3B%2C%3CBR%20%2F%3E%3CBR%20%2F%3EBoth%20my%20time%20accepted%20and%20time%20delivered%20are%20single%20line%20of%20text%20because%20in%20my%20powerapps%2C%20I%20use%20Now()%20function%20to%20capture%20the%20time%20and%20date.%20Now%20that%20I%20want%20to%20calculate%20the%20time%20difference%2C%20I%20referred%20to%20the%20formula%20you%20sent.%20It%20returns%20an%20error.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AloisOhZH_0-1605853915882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235115iD30DD91D76390D3B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AloisOhZH_0-1605853915882.png%22%20alt%3D%22AloisOhZH_0-1605853915882.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3EAlois%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1916707%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20Time%20difference%20in%20calculated%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1916707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F825020%22%20target%3D%22_blank%22%3E%40AloisOhZH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20save%20Now()%20from%20Power%20Apps%20to%20SharePoint%20date%20and%20time%20fields%2C%20check%20below%20reference%3A%3C%2FP%3E%3CP%3E%3CA%20title%3D%22Sending%20Date%20and%20Time%20to%20SharePoint%20Column%20Type%20Date%20and%20Time%22%20href%3D%22https%3A%2F%2Fpowerusers.microsoft.com%2Ft5%2FBuilding-Power-Apps%2FSending-Date-and-Time-to-SharePoint-Column-Type-Date-and-Time%2Ftd-p%2F530892%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESending%20Date%20and%20Time%20to%20SharePoint%20Column%20Type%20Date%20and%20Time%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20what%20is%20the%20error%20you%20are%20getting%20using%20above%20formula%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3C%2FSTRONG%3E%3A%3C%2FP%3E%3COL%3E%3CLI%3ESometimes%20comma(%2C)%20does%20not%20work%20in%20formula%20(I%20am%20not%20sure%20but%20it%20is%20based%20on%20something%20language%20or%20regional%20settings%20on%20your%20site).%20So%20in%20that%20case%20use%20semicolon(%3B)%20instead%20of%20comma(%2C).%3C%2FLI%3E%3C%2FOL%3E%3CP%3EFor%20correct%20syntax%2C%20check%20this%3A%26nbsp%3B%3CA%20title%3D%22Calculate%20the%20difference%20between%20two%20times%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fdeveloper%2Fsharepoint-2010%2Fbb862071(v%3Doffice.14)%23calculate-the-difference-between-two-times%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ECalculate%20the%20difference%20between%20two%20times%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3CP%3EPlease%20click%20%3CSTRONG%3EMark%20as%20Best%20Response%3C%2FSTRONG%3E%20if%20my%20post%20helped%20you%20to%20solve%20your%20issue.%20This%20will%20help%20others%20to%20find%20the%20correct%20solution%20easily.%20It%20also%20closes%20the%20item.%20If%20the%20post%20was%20useful%20in%20other%20ways%2C%20please%20consider%20giving%20it%20%3CSTRONG%3ELike%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1918123%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20Time%20difference%20in%20calculated%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1918123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373269%22%20target%3D%22_blank%22%3E%40ganeshsanap%3C%2FA%3E%26nbsp%3Bas%20you%20have%20the%20dates%20%26amp%3B%20times%20in%20Power%20Apps%20you%20could%20just%20do%20the%20DateDiff%20calculation%20in%20Power%20Apps%20then%20save%20the%20result%20to%20the%20SharePoint%20list%20item.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DateDiff-formula.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235171iB09EE373D8456C78%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22DateDiff-formula.png%22%20alt%3D%22DateDiff-formula.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DateDiff-Result.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235172i2771E71B06119ACE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22DateDiff-Result.png%22%20alt%3D%22DateDiff-Result.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

How do i calculate the time difference between time delivered and time accepted. Both of the columns are single line of text.

My time taken column is a calculated single line of text type. 

AloisOhZH_0-1605767018417.png


Regards,
Alois

5 Replies

@AloisOhZH 

Can you please share the formula you are currently using for "Time Taken"?

That will help us to identify the issue with formula and answer your question more precisely.

 

Also, why are you using single line of text fields for Time Accepted and Time Delivered?

 

General format for your formula should something be like:

=TEXT(DATE([Column2])-DATE([Column1]),"h:mm:ss")

ReferenceDate and time formulas 


Please click Mark as Best Response if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

 

Hi @ganeshsanap ,

Both my time accepted and time delivered are single line of text because in my powerapps, I use Now() function to capture the time and date. Now that I want to calculate the time difference, I referred to the formula you sent. It returns an error.

AloisOhZH_0-1605853915882.png


Regards,
Alois

@AloisOhZH 

You can save Now() from Power Apps to SharePoint date and time fields, check below reference:

Sending Date and Time to SharePoint Column Type Date and Time 

 

Also, what is the error you are getting using above formula? 

 

Note:

  1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

For correct syntax, check this: Calculate the difference between two times 


Please click Mark as Best Response if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

 

@ganeshsanap as you have the dates & times in Power Apps you could just do the DateDiff calculation in Power Apps then save the result to the SharePoint list item.

 

DateDiff-formula.pngDateDiff-Result.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Hi @ganeshsanap ,

I tried what u seggest but I get an error with both comma (,) and semicolon (;). Do u have any other trick to share?

 

rispazia_1-1619270527090.png

 

 

rispazia_0-1619270491273.png