How To Use SharePoint List Calculated Column Date Formulas

hey there louis acabalis here thanks for stopping by in this tutorial i'm going to show you how you can create calculated columns in a sharepoint list that uses date formulas to calculate the current date and to extract the day month and year from another date column in your list now before we get started if you find this tutorial helpful please hit that thumbs up below and be sure to subscribe to stay up to date on the latest tutorials that i publish now let's go ahead and let's get started all right now in this tutorial we're going to look at how you can create calculated columns that use date formulas specifically we're going to look at how to create a calculated column that displays the current date we're also going to look at how to create a calculated column that displays the difference between another date field and the current date and we're going to look at how you can create a calculated column to extract parts of another date field specifically how you can display the year the month and the day of another date field so we're going to start by creating a calculated column that displays the current date now to create a calculated column you want to click on the drop down arrow to the right of the add column option here and you want to click on the more button next you want to give this column a name so i'm going to go ahead and call this invoice dash age and in the column type you want to select calculated next you want to scroll down and in the formula field you want to enter equals and you want to enter the formula today followed by an open and closed brackets next you want to set your return type and i'm going to go ahead and select date and time and you can see here you have the option of either displaying just the date or the date and time so you can select whichever one is appropriate for you i'm going to leave this as date only and i'm going to scroll down and click ok now you can see here that the column was added and it is also displaying the current date so today is april 13th so that's how to create a calculated column that displays the current date now an important note when using the today formula this is only going to update whenever you actually create or edit an existing item in your sharepoint list so this isn't dynamic in the sense that it is going to update on its own it will update when you add an item to a list or when you modify an existing item or when you close out and navigate back to the sharepoint list next we're going to look at how to create a formula that calculates the difference between two date fields now specifically what i'm going to implement is a formula that calculates the age of an invoice and the age of an invoice is defined as the difference between the current date and the invoice date now i will go ahead and repurpose the invoice age column that i already created so i will click on the settings icon and i will click on list settings next i'll scroll down and i will click into my invoice age calculated column and in order to calculate the difference between the current date and another date field i'm going to add a subtraction sign here and i'm going to go ahead and select my invoice date column so you can see here my formula that reads equals today minus invoice date next you want to select your return type i'm going to go ahead and select number because i want this to return an integer and i'm going to set my decimal place to zero next you want to scroll down and click ok and i'm going to click on the name of my list to get back into my list and you can see here that the invoice age is displayed as an integer so you can see that this first invoice invoice number two is 433 days old you can see that invoice 3 is 8074 days old etc so this is calculating the difference between the current date which is april 13th 2022 and the invoice date that is listed here so that's how you can calculate the difference between the current date and another date field in a sharepoint list all right next we're going to look at how to create a calculated column that extracts the month of another date column and displays it in three different formats so we're going to look at how to display the full month name we're going to look at how you can display the first three characters of the name of the month and then we're going to look at how you can display the month as an integer value from 1 to 12. now again to create a calculated column you want to click on the add column drop down and you want to click on more you want to go ahead and give your column a name so i'm going to call this column invoice full month name for demonstration purposes next you want to click on calculated and next you want to scroll down and in the formula field if you want to extract the full month name from another date type column what you want to enter is the following formula so i've just pasted this in here you can see the formula reads equals text open bracket then there's a comma and it's followed by a quote set of quotation marks with four capital m's now really important what you need to do is you need to actually insert the name of the column that you want to reference just before the comma so you can see here i've placed my cursor just before the comma and i'm going to double click invoice date now you can see here that it didn't actually pipe it into the right spot so i'm just going to copy that and paste it here just before my comma and this is going to return the full month name next you want to set your return type now because this is going to return the full month name i'm going to leave this as a single line of text and then you want to scroll down and click ok and what you can see here is that this new column has been added you can see invoice full month name and it is displaying the actual name of the month that corresponds to the month cited here in this invoice date field so you can see january 4th will display january march 5th will display march and february 2nd will display february so that's how to extract and display the full month name from another date column in a sharepoint list next we'll look at how you can return just the first three letters of the month name all right so i've gone ahead and created my calculated column and now i'm going to scroll down and this time in the formula field i'm going to paste this formula so it's the same formula that we used in the previous step you'll just notice this time instead of four m's there are only three and again what we want to do is we want to insert the date column that we're referencing just before the comma so i've put my invoice date reference just before that comma and i'm going to go ahead and make sure that i've set the right return type so we're going to leave this as a single line of text again and i'm going to scroll down and click ok and i'll scroll over and so you can see here this new column that has been added invoice month first three and again all this one is doing is displaying the first three characters of the field that we're referencing which again in this case is the invoice date field next what we're going to look at is how to extract the month from another date field but this time it's going to return the month as an integer all right so you can see here again i've created my calculated column and i'm going to scroll down and in the formula field i am going to paste in the same formula that we used in the previous step again except this time we are only going to include two m's now recall you want to make sure that you pipe in the reference column and place that just before the comma in this formula so i've added my invoice date now this one is going to return an integer so you have a few different options you could set the return type to number or you could set it to a single line of text it really depends on what you're planning to do with this column if you're going to be using this column to derive other calculated columns or for the purposes of formulas or validation in workflow then it probably makes sense to set this to a number i'm just going to leave this as a single line of text next i'm going to scroll down and click ok and i will scroll over and you can see here this time that our invoice month integer column is tabulating or calculating the month and displaying that month as a numerical value of 1 to 12 where each number represents the specific month so you can see here february 4th 2021 displays as o2 march 2000 displays as o3 january and january display as 01. so that is how to create a calculated column that extracts the month of another date field in a sharepoint list and display it in three different formats all right next we're going to look at how to create a calculated column that extracts the year from another date field in your sharepoint list so again to create a calculated column you want to click on the add column drop down and you want to click on more you want to give your column a name so i'm going to call this invoice dash year you want to select calculated and you want to scroll down and in the formula field again we're going to reuse the formulas that we used in the previous step now you'll notice here that because we're talking about extracting the year we change the m's to y's and we are going to display four y's in this formula so equals text open bracket then there's a comma and the quotation marks with four years close bracket now remember you also need to pipe in the reference column so i'm going to go ahead and grab my invoice date column and you want to place your reference column just before the comma next you want to set your return type and again because this is going to return uh numerical value it's okay to either use a single line of text or a number i'm going to use a single line of text and next you want to scroll down and click ok and you can see here our invoice year column is extracting the year from our invoice date column so in this case 2021 is displayed here the year 2000 the year 2000 the year 2000 etc all right now we're going to end off here by looking at how you can create a calculated column that extracts the day from another date column in your sharepoint list now again to add a calculated column you want to click on the add column drop down and you want to click on more you want to give your column a name so i'm going to call this invoice day you want to select calculated in the list here and you want to scroll down and in the formula column again we're going to leverage the same formula that we used in the previous steps the formula is equal text open bracket comma and this time we are going to populate four letter d's inside of our quotation mark now recall you want to make sure that you populate your reference column just before the comma so i'm going to go ahead and select my invoice date column and i will paste it just before the comma now with this formula it is going to actually return the full name of the day of the week so i'm going to set the return type to single line of text and next you want to scroll down and click ok now you can see here the invoice day column is displaying the name of the day of the week based on the invoice date so thursday sunday tuesday and tuesday so next we're going to look at how you can create a calculated column that displays the day of the week represented as a numeric value from one to seven where one represents sunday and seven represents saturday all right now you can see here that i've created my column and i've selected calculated as the column type next i'm going to scroll down and in the formula field i'm going to paste the formula equals weekday open and close bracket now you want to make sure that you pipe in your reference column so i'm going to take my invoice column and paste it in between my brackets and this is going to return a numeric value on a range of one to seven where one represents sunday and seven represents saturday now you can set your return type to either single line of text or number i'll leave it to single line of text and i'll scroll down and click ok next i'll click on my list name to bring me back into my list and you can see here that the invoice day int column is displaying a numeric value from 1 to 7 so the one represents sunday two represents monday three represents tuesday etc all the way to seven so that's it in this tutorial i showed you how you can create a calculated column in a sharepoint list to extract different parts of a date field specifically we looked at how to create a calculated column that takes the current date that calculates the difference between the current date and another date field in your sharepoint list and then we looked at different ways that you can extract the day the month and the year from another date field in your sharepoint list now remember i've pasted all of the formulas in the description of this video below so you can actually just click that description and grab the different formulas so that you can use them in your sharepoint list and so i hope you found this tutorial helpful if you did please remember to hit that thumbs up below and remember to subscribe to stay up to date on the latest tutorials that i publish i'm louis echo balls thanks for stopping by talk soon

Channel: Lui Iacobellis

Views: 47,824

Keywords: Sharepoint list, sharepoint list calculated column for date and time, date formula sharepoint list, date formula sharepoint, sharepoint list column date and time calculated value, sharepoint list calculated column date difference, sharepoint calculated column today, sharepoint calculated column month, sharepoint calculated column month and year, sharepoint calculated column days old, sharepoint calculated column days until due date, sharepoint calculated column weekday

Id: ZjsNY7Q1RIg

Length: 15min 48sec (948 seconds)

Published: Mon Apr 18 2022