How to Create Date Difference in SharePoint List Using Calculated Column

[Music] hi everyone and welcome to my youtube channel where you learn everything about analytics automation and productivity do you have a sharepoint list with two dates or more than two date columns and you want to calculate the difference between the dates in case you want to generate additional column that track how long it takes for a particular items to be resolved or the difference between when an approval was submitted and when it was actually approved if that is the situation right now then this video is for you so in this video i'm going to show you how to create a calculated column that's what i'll calculate the differences between two dates on the sharepoint list so where do we start from all right we're going to create a new column this time around we're not going to create just the usual column that you can see right here i'm going to navigate to more and click on more more will take me to an environment where i can create different columns um you know modern options that i have on my list page as you can see right now i have several type of columns here column type i'm just going to label this um i mean um lag time like okay let's just call it lag time or less so time to resolve okay time to resolve time difference let's just leave it as time to resolve when i'm creating column i often make sure i write my column in column name in karma case like not leaving space here you're going to navigate straight to calculated column and calculator column allows you by the name calculated it allows you to create a column that does calculation based on other columns whose values are based on input from other coulombs and immediately i click on this the screen just depend on your network speed and show me this formula where i can directly type in my formula my expressions and then run the calculation similar to excel every formula starts with equal sign same here as well so i'm going to start with equal sign and let's just find the difference between the two different time you know dates that we have here which is just the date the issue was logged which i have here which is dead logged i'm just going to say my notes the date it was resolved so let's see how this works out i'm going to click on ok all right this column has been created and all i have here is wow i'm not getting a consumable value here this doesn't sort of make any sense to me i just have numbers and dots and some is negative is negative because that it was logged was less than date it was you know it was resolved right so let's go back and change it to this i'm going to change it so i will locate the column which is time to resolve i will click on time to resolve the expression we're going to use here i'm going to start from scratch now equal sign date diff so that div will calculate the date difference but remember we need it in a particular order or format so i'm going to put in the date it was logged first that logged comma so i'm using this to calculate the difference between date it was log and date it was resolved and i need to specify putting comma specify in what format do i need it i need it in days so when i put just d it will give me just the days the days different the difference in days i'm going to close this bracket but because i want it to be something readable so i'm going to concatenate by using the art symbol and then concatenate days so whatsoever number that the formula you know resolve your results to you're going to append days so now maybe 30 days four days but that is not all so let me leave this place so that can be space between the other expression i want to write the next formula is i want to append again so give me it in days in as in minutes and in seconds so i'm just going to use add sign to concatenate because i want to extract just the text i want to extract the minutes the hours and seconds i need to use text here and not not date div so i'm going to type the test expression open my bracket this time around i want the difference between the date logged minus date resolved gonna be dead logged i'm gonna put minus sign just the way we had it earlier on get result so i have that log man update result now and i need to specify a comma just the same way we did it for the date difference what do i need the hours just return the hours difference so i've specified my format when i'm formatting the text i'm going to close don't forget because i wanted to read hours i'm going to concatenate and had h i'm going to have hr let's root s meaning us it's going to tell us how many hours all right great i'm going to leave space to so that it could be space um i'm also going to concatenate again this time around we need to just repeat this i'm going to repeat it from this test control i mean ctrl c to copy i'm going to paste this time around i only need it in minutes so i'm going to m is for minute and i'm going to change the apparel the text that has been appended to m i n you know and again append again you know concatenate using apps and paste that same formula this time around change this to ss which is seconds and of course you can send your sxc which is second right now if you look at this i have been able to express that i've been able to write the expression of formula that does the calculation first thing is to look at this date locked and the date the issue was resolved how many days different we have there returned that the difference in this and not a month nothing yes i need that indeed because we expect the issues to be resolved in days no matter how long you know 30 days yeah 40 days or depending but most of the time should be in days okay what about the hours and the minute and the seconds difference that is why we are using the text to format the difference between date and no date logged and date resolved oftentimes it will return them in numbers but you can extract them in hours which you are saying that give me this in hours and append hours hrs as a text give me uh also a minute and append the minutes you know as a text label give me in seconds and also append it let's click on ok and see how this and it's going to work out now that i have okay okay let's let me click on mini case maker now to go back to that script wow awesome do you see that we have them here seven days one hours twelfth minute and zero seconds 21 days 21 hours 12 minutes you know and zero seconds you can see that i'm going to create a new item just to show you right now i'm going to just click type or latch us as the subject here is your complaint the date log let's lock this sometime yeah and the time i'm just going to make the time 12 45 pm now okay and the status is the open you just want to see the time difference the date result actually you might not have your own yours as date resolved whatever the column is let's assume the date is even being populated by an action card or by one activity but you are interested in calculating the difference between these two dates so i'm going to come here and assuming it was resolved on close to that time is um maybe 18 because we lodged it on and this 18 instead of 12 maybe one you know 1 35 p.m sort of fine so within 13 and 18 just like five days difference i'm gonna click on save and right here you see do you see that five days zero hours twelve minutes and zero cycles and by the way i have a flow at the back end that is generating the mini the case id you know is a custom value and it's generating it in case we want to see how to generate that unique value just check at the top here there's a video you know which is coming up now that video shows you how to generate a unique a custom unique id auto numbered id in sharepoint i hope you enjoyed that all right so do you see that it has been generated and we are done and thank you don't forget to you know subscribe to my youtube channel and also you know click on the notification bell so i can get notified for subscribe videos as i drop them thank you and bye for now

Channel: TheOyinbooke

Views: 31,844

Keywords: sharepoint list, sharepoint, date difference in sharepoint, calculated column, theoyinbooke, oyinbooke, how to calculate date diff in sharepoint, how to calculate date difference in sharepoint, difference between two dates, microsoft sharepoint, sharepoint online, microsoft 365 sharepoint, sharepoint update, sharepoint list column, calculated column type in sharepoint

Id: NCfAuWvUWxQ

Length: 9min 25sec (565 seconds)

Published: Mon May 31 2021