How To Use SharePoint List Calculated Column Date Formulas

Подписаться 15 тыс.

Просмотров 51 тыс.

50% 1

This SharePoint tutorial will demonstrate how to use date formulas in a SharePoint list. Specifically, it will demonstrate how to create a SharePoint List calculated column that uses date formulas. It will demonstrate how to create a SharePoint List calculated column that calculates today or the current date. It will also demonstrate how to create a SharePoint List calculated column date difference that calculates the difference between the current date and another date field. This SharePoint calculated column days old technique is perfect for determining the age of an item or for determining the number of days until a task is due (or is overdue). It will also demonstrate how to create a SharePoint list calculated column month, which extracts the month from another SharePoint list date field. It will also demonstrate how to create a SharePoint list calculated column year that extracts the year from another SharePoint list field. It will conclude by demonstrating how to create a SharePoint List calculated column weekday that extracts and displays the name of the day of the week from another SharePoint list column and as an integer. All of the SharePoint list date formulas referenced in this tutorial have been added below for reference:
🔔SIGN UP FOR MY SHAREPOINT LIST FUNDAMENTALS COURSE🔔bit.ly/462FHc4
FORMULAS USED IN THIS TUTORIAL
SharePoint List Calculated Column Current Date Formula:
=Today()
SharePoint List Calculated Column Month Formulas:
=Text(, "MMMM") - This will return the full month name
=Text(, "MMM") - This will return first 3 letters of the month name
=Text(, "MM") - This will return integer month
SharePoint List Calculated Column Year Formulas:
=Text(, "YYYY") - This will return the year
SharePoint List Calculated Column Day Formulas:
=WEEKDAY() - Returns an integer representing the day component of the date field
Timeline:
0:00 - Start
1:05 - SharePoint List calculated column current date formula
3:30 - SharePoint List calculated column date difference formula
5:25 - SharePoint List calculated column full month name formula
7:30 - SharePoint List calculated column first 3 characters of month name formula
8:40 - SharePoint List calculated column month as an integer formula
10:20 - SharePoint List calculated column year formula
12:00 - - SharePoint List calculated column weekday formula
#Sharepoint #Microsoft365 #microsoft
MORE ABOUT ME
LOOKING FOR TRAINING OR CONSULTING - CONTACT ME AT:
bit.ly/3OLDmbh
LETS BE FRIENDSI
🔔Instagram: / luigiiacobellis
🔔LinkedIn: / liacobe
🔔Twitter: / luigiiacobellis
🔔TikTok: / luigiiacobellis
🔔Reddit: / owndirector1326
🔔Website: luigiiacobellis.com/
🔔Linktree: linktr.ee/Liacobellis
GOT A VIDEO IDEA? FILL OUT THIS FORM:
bit.ly/3yjEewm
SIGN UP FOR MY MONTHLY NEWSLETTER
📧 landing.mailerlite.com/webfor...
PRODUCTS THAT I USE TO CREATE MY RU-vid VIDEOS:
⭐ Amazon StoreFront: www.amazon.com/shop/luiiacobe...
⭐USB Microphone - Yeti X: amzn.to/3QLpfVt
⭐ Web Cam -Dell UltraSharp HDR 4K: amzn.to/3HQ9TuO
⭐ Laptop - Dell XPS 9510: amzn.to/3ng1zL9
⭐ Monitor - Dell Curved 4K UHD: amzn.to/3OKjCoL
⭐ Mouse-Keyboard - Dell Premier Multi-Device: amzn.to/3OlI9kf
⭐ Lighting Kit - LimoStudio 700W Series: amzn.to/3u1V82q
SOFTWARE THAT I USE TO CREATE MY RU-vid VIDEOS & CONTENT:
⭐ Screen Recording & Editing - Camtasia: bit.ly/3NmF9mf
⭐ Screenshots & Photo Editing - SnagIt: bit.ly/3NmF9mf
⭐ RU-vid Thumbnails - Canva Pro: bit.ly/3nj1Rkx
⭐ Content Tracking - AirTable: bit.ly/3dYsV3H
⭐ Email Newsletter Creation - MailerLite: bit.ly/3ET6SYb
⭐ Mailing List Forms - ConvertKit: bit.ly/3xVD5ff
⭐ Social Media Scheduling - Later: bit.ly/3lgeCuW
⭐ VPN - ExpressVPN - 30 Days Free: bit.ly/3NmGUjl
⭐ Task Tracking - Trello: bit.ly/3xToX69
OTHER SERVICES THAT I USE:
⭐ ETF Investing - WealthSimple - Get $10,000 Managed Free: bit.ly/3HQF8G5
⭐ IT Training - Global Knowledge - Save 10%: bit.ly/3A6l5BG
⭐ Cashback On Online Purchased - Rakuten - Get $30 Free: bit.ly/3OmNV59
⭐ Earn Passive Income - Survey Junkie: bit.ly/3OoXwZ7
Note that I may earn a small commission at no additional cost to you from purchases made using these links.

Наука

Опубликовано:

21 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 83

@LuiIacobellis 11 дней назад

🔔SIGN UP FOR MY SHAREPOINT LIST FUNDAMENTALS COURSE🔔 bit.ly/461F4iX

@spchengQ1 Год назад

Thanks Luigi, it solved my issue after watching your video! Kudos to you.

@LuiIacobellis Год назад

You're very welcome and thanks for watching

@LuiIacobellis Год назад

CHECK OUT MY HOW TO USE SHAREPOINT PLAYLIST - 40+ TUTORIALS: ru-vid.com/group/PLmE7KGV9-I4uibXaJ7ZqTXbbS4tukRJPh

@phileaster7141 Год назад

Lui, just ran across you videos. I like your style. Subscribed!

@LuiIacobellis Год назад

Hi Phil, thanks for the kind words and for subscribing. Talk soon!

@briangaulrapp8989 Год назад

Hi Lui. Is it possible to use the calculated date columns to display on a calendar overlay? I've had issues getting these columns to display versus normal date columns. Any advice would be greatly appreciated. Thanks!

@jjajwjw Год назад

Your videos are really informative and effective, keep going

@LuiIacobellis Год назад

Thanks Rahul! I'm glad you find them helpful!

@angelogilio9565 4 месяца назад

Hi MAN! First of all THANKS for your help you very help full! I have a question for you. Is possible to re-set a date list filed according month? For example i need that after having approve by email a job the specific date reset itself one year later or two months later and so on...

@Todd-W Год назад

Lui, thank you for this! It gave me a clue as to what needs to be done however I'm still struggling. I have an existing list with a date column C (expiration date) and a column A that is labeled (active/inactive). I would like for the list to look at expiration date column and if it's before TODAY, then set column A (active/inactive) to INACTIVE. Can you give me pointer on how to accomplish this please sir.

@LuiIacobellis Год назад

Hello, my apologies for the delay in responding to your comment. I hope that you found an answer by now. If not, I believe I cover what you’re looking for in this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bz1qSexNW9Y.html . Hope this helps and thank you for watching

@biesalles5859 Год назад

Hi Lui! How are you? Your videos are awesome, thanks for that! I'm trying to create a column that tracks the date and time based on the status of another column. The goal here is to have a TIMESTAMP of when the status is Resolved. So that I can have the duration of the task since entered at the list to the time is was resolved. Do you have a video that can help me with this situation?

@LuiIacobellis Год назад

Hi there, unfortunately I do not have a specific video that covers your exact use case. However, it does sound like your use case might be best satisfied by building a workflow in Microsoft Power Automate that records the time when the status changes. Alternatively you could try and built out a calculated column using statements that do the same but that might be a bit more difficult. Here is a link to my tutorial on IF statements: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bz1qSexNW9Y.html - hope this helps

@fanzfanzilla Год назад

Hello, Thank you for the video. As you mentioned the calculation is not dynamic and requires that item is updated/edited before calculation occurs. Is there a way around it? Is only way to achieve this by using workflow? The workflow will cause the meta data to be updated. For example, if workflow updates a field in the list it then sharepoint will generate new version, last updated and last update date will change. Do you have recommendations or thoughts?

@LuiIacobellis Год назад

Hi there, unfortunately the only workaround to that limitation in a list is to use workflow. If you require live fields that update regularly then a SharePoint list may not be the best solution and you may want to consider a Power App. I hope this helps and thanks for watching

@salomonmurave160 Год назад

Thanks Lui, great video. One question. Is there a way to calculate the week of the year, from a date in another column?

@LuiIacobellis Год назад

Hi there, yes this is possible. I came across this article which may be what you are looking for techcommunity.microsoft.com/t5/sharepoint/formula-calculated-column-show-date-as-week-number/m-p/3291504

@moisestatis5418 Год назад

good video, thanks.

@LuiIacobellis Год назад

Moises Tatis You're very welcome! Thanks for watching!

@ryancockrel6947 9 месяцев назад

Do you have a video that shows how to calculate the date of a specific weekday. My intention would be to have a calculated default date that is the next saturday from whenever the item was created. Thanks for your content, you've been really helpful to me!

@LuiIacobellis 8 месяцев назад

Sorry for the delay unfortunately I do not have a video outlining how to do this at this time but this is on my backlog. Thanks

@jordansmith9230 Год назад

Hi Lui, brilliant video. How would you write a formula to generate a column that is + 1 year from the date in another column?

@LuiIacobellis Год назад

Hi Jordan, thanks for the comment. Your formula would be the date + 365 to add in the additional time. Hope this helps!

@user-rj2lc9cv2r 4 месяца назад

Hi There, your videos are very useful however, I’m struggling to find a formula that will assist in saying: If date in ‘column A’ is todays date or earlier change ‘column B’ to word “overdue”. Can you help me?

@caleblauber6613 Год назад

What about generating a future date in the Date column using the default value formula option? Example... if I create a "Revision Log" date column and want it to generate the date 180 days from when it was last modified.

@LuiIacobellis Год назад

Hi Caleb, for this you will need to use a calculated column type and not a date type. In date type columns, you cannot reference other columns in the calculated value field. Therefore, to do this, create a calculated column and set the formula to [Modified]+180 and set the return data type to date. Hope this helps and thanks for watching!

@area_2111 2 года назад

Hello thanks for the video, Is it possible to set "date and time" by the end date of each month?

@LuiIacobellis 2 года назад

Hi there, are you asking if its possible to set a calculated column to the last day of the month? If so, it is possible using date formulas within the calculated column. Here is a resource that can help you get started on the formula: www.johnluangco.com/2016/07/sharepoint-default-value-end-month.html - Hope this helps! thanks for watching.

@jasonhampton 4 месяца назад

Is it possible to have two formulas embedded in the sharepoint list? The first formula is TODAY()-Creation Date

@vijaysharma7677 Год назад

Hello Lui Need your help with a quick query I am using a SharePoint list where I have start and end dates below are the things that I wish to achieve 1. Calculate the difference between the two dates that includes the start date and excludes weekends and holidays if any

@LuiIacobellis Год назад

Hi Vijay I'm working on a tutorial to cover this scenario. In the meanwhile here is an article that might be helpful techcommunity.microsoft.com/t5/sharepoint/calculate-working-days-in-sharepoint/td-p/1081664 thanks

@MadanKumar-uy4iu 2 месяца назад

Good one

@LuiIacobellis 2 месяца назад

Thank you! Cheers!

@kenyonsmith2369 2 года назад

This is a great tutorial, thank you!! How would I have the formula ignore the column it is looking at, if the column is empty? I have a Column called CHA Date which is taking the number of days from the CHA Days Column (displays the number of days) and is returning the date. It works fine, but is also putting today's date when the CHA Days column is empty. How can I have it ignore when CHA Days doesn't contain any data or at the very least, have it display nothing? CHA Date (Calculated Column) with this as the formula =TODAY()-[Last CHA (Days)]

@LuiIacobellis Год назад

Hi there, sorry for the delay! if you are looking for assistance with building out IF statements in SharePoint, I would suggest you visit the Microsoft Tech Community and post your question there: techcommunity.microsoft.com/ Hope this helps and thanks for watching!

@kennyfabian2126 2 года назад

Hello Lui, i am trying to do something that someone noob would think is easy based on logic but it isn´t. I just want a formula with the difference in days between two dates and excluding the weekends, is this possible? Simple example, three columns: Start date: 01/07/2022 End date: 15/07/2022 Difference: 14 (number format) I want to exclude 4 days because of weekends, making it a 10 days difference. Thanks in advance if you get to see and answer this comment. PD: Im from the DR (Latin America), thats why the dd/mm/yyyy format.

@LuiIacobellis Год назад

Hi Kenny, I hope you're well. Here is an article on stack exchange that has a formula to calculate the difference in working days between 2 dates. Based on the comments, it seems to work. I've not tested it myself however. Let me know if you figure it out! Thanks for the comment and for watching. sharepoint.stackexchange.com/questions/197827/calculate-number-of-working-days-between-two-dates

@giggleberriescouk Год назад

Hi, How would I set the column to express the age of a person in years & months (i.e 17.5 = 17 years and 5 months old) using the formula =today()-date of birth? Thanks

@LuiIacobellis Год назад

Hi there, I've come across this post on the Microsoft Tech Community that might assist with your query. techcommunity.microsoft.com/t5/sharepoint/lists-formulas-for-calculate-age-from-date-of-birth/m-p/3593267 - Hope this helps and thanks for watching!

@alfonsolans Год назад

Great Stuff Buddy thanks for sharing it AWESOME, quick question what about I have a entry date and exit date, If I want to create a calculated estatus column based on entries and exits, for example entry "" and exit = "" is On Site, entry "" and exit "" is Out and entry = "" and exit = "" is Not Registered, I have tried everything on it and I am always getting the same error after I hit the OK button, THanks in advance for your suggestions and Keep Rocking Buddy

@LuiIacobellis 10 месяцев назад

Hi there, thank you so much for the kind words. Unfortunately, I can't provide support with troubleshooting SharePoint calculated column formulas. I would need to see the full context to do this. I'd suggest posting the formula over on the Microsoft Tech Community as you will likely be able to get a fast and more detailed response. You can access it at this link: techcommunity.microsoft.com/ - Hope this helps and thanks for watching!

@andrecosta612 Год назад

Hi, How can I make my current date (today) update automatically an of course without changing anything of my columns? Do you have any recommendations or ideas?

@LuiIacobellis Год назад

Hi there, unfortunately the only way to do this would he to use a workflow via Power Automate or to develop a Power App. Both allow for functions to be built that can add realtime values. Hope this helps and thanks for watching

@lemuel_adrian 6 месяцев назад

Hi Lui, is it possible to calculate the total number of seconds spent on a specific work in Sharepoint?

@LuiIacobellis 6 месяцев назад

If you are asking if you can record the time a user spends working in SharePoint, that's not a capability provided by SharePoint. You'd need a time tracking solution for that.

@lemuel_adrian 6 месяцев назад

@@LuiIacobellis thank you. Been trying to figure out the formula and you finally answered the question. Bummer that its not possible. 😔

@tedbenson1958 Год назад

One small error in the nested IF statements. The left parameter in the IF/AND statement should be >=, or a size of 250 would cause a null result

@LuiIacobellis Год назад

Thank you for catching this error! Much appreciated

@kannetimahesh836 7 месяцев назад

Hi sir, I need a calculation to find week number in SharePoint list pls give me advise.

@LuiIacobellis 6 месяцев назад

I'll add this to my backlog. In the interim, I would suggest posting this question over on the Microsoft Tech Community as you will likely be able to get a fast and more detailed response. You can access it at this link: techcommunity.microsoft.com/ - Hope this helps and thanks for watching!

@hendrakusuma5523 Год назад

ask sir, how to calculate the age of the invoice based on working days?

@LuiIacobellis Год назад

Hi there, I would suggest you post this question with more detail over on the Microsoft Tech Community: techcommunity.microsoft.com/ - Hope this helps!

@genesisbrito7209 5 месяцев назад

How calculated colum with this formula: =IF(Categoria="Baja";F_Solicitud+21;IF(Categoria="Media";F_Solicitud+14;F_Solicitud+7)), adding only business days? Thanks!

@LuiIacobellis 5 месяцев назад

Unfortunately I can't provide this type of support via comments. I'd suggest you post your question over on the Microsoft Tech Community

@LuiIacobellis 5 месяцев назад

Unfortunately I can't provide this type of support. I'd suggest you post your question over on the Microsoft Tech Community

@user-gd5ni3cz9z 2 года назад

Can i calculate ISO string from date?

@LuiIacobellis 2 года назад

Hi there, this may be possible using Microsoft Power Automate. I've never done this myself unfortunately. Here is a thread that might help. Thanks for watching! powerusers.microsoft.com/t5/Building-Flows/Datetime-string-must-match-ISO-8601-format-Can-t-figure-it-out/m-p/910092#M128076

@simplevideos8605 11 месяцев назад

How to calculate no of days on below conditions I have open date as yesterday But do not have close date?

@LuiIacobellis 10 месяцев назад

Hi @simplevideos8605 there, I would suggest posting this question over on the Microsoft Tech Community as you will likely be able to get a fast and more detailed response. You can access it at this link: techcommunity.microsoft.com/ - Hope this helps and thanks for watching!

@juliowastaken 9 месяцев назад

Are you able to hide date field until a column value is selected?

@LuiIacobellis 9 месяцев назад

This video covers conditional hide show of field - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-V9ysKQRMJ00.html - it might help address your question

@selinarousseau5055 10 месяцев назад

Hiya - thanks for your easy to follow steps.... So many great options - however -- could you help me replicate an excel formula to extract total age of an asset in SharePoint. This is my current formula coming off the Warranty start date: =DATEDIF([Warranty start date],TODAY(),"y")&" Yrs "&DATEDIF([Warranty start date],TODAY(),"ym")&" mths " I am happy for it to just show up as a number like 2.3 (like two years and 3 months old Any help would be great - thanks

@LuiIacobellis 9 месяцев назад

Hi there I'm glad you found this video useful. Unfortunately I cannot assist with adhoc requests to develop formulas sorry. I would suggest posting the inquiry over the Microsoft Tech Community as someone will likely he able to support you. Thanks

@DevendraSingh-tx2pt 2 года назад

How to update sharepoint list to get current date in real time. In my case If I export list to excel it does not show current date it has date when I updated last time. Please advise

@LuiIacobellis 2 года назад

Hi there, to do this you would want to create a calculated column that uses the =TODAY. However, this calculated column will only show the current date if an item is edited. Else, it will show the current date based on the last time an item was edited. Hope this helps!

@DevendraSingh-tx2pt 2 года назад

@@LuiIacobellis Thanks for reply. I have used same formula for current date but problem is it does not update automatically. I have to calculate due date on the basis of current date. But don't want to modify records

@abbasthegreat5 Год назад

All the dates are in UTC timezone. How to change that in Current/Local timezone?

@LuiIacobellis Год назад

Sorry for the delay see this article support.microsoft.com/en-us/office/change-regional-settings-for-a-site-e9e189c7-16e3-45d3-a090-770be6e83c1a - hope this helps and thanks for watching

@mandysingh3878 2 года назад

How to use networkdays btw two dates

@LuiIacobellis 2 года назад

Hi there, I've added this to my backlog & will be publishing a tutorial on this topic in the coming weeks. Thanks for watching!

@thalapanenirakesh8459 Год назад

How we can do calculated column based on start date and end date

@LuiIacobellis Год назад

Hi there, you should be able to create a calculated column that references both a start date and end date you have already created in your list. I hope this helps and thanks for watching

@lesliecaldwell3131 2 года назад

I need to add a column that auto populate a due date

@LuiIacobellis 2 года назад

Hi there, you can definitely use this approach go do that. You can add a predetermined amount of time to another date column. For example you can create a formula with the created column + 45 days if that was your definition of a due date. Hope this helps!

@jackemanalo7741 2 года назад

Hello I am trying to create a automatic column with a Pass or Fail result from 5 parameters however i cant get the right result or none is working. Help😭

@LuiIacobellis 2 года назад

Hi there, it seems as though you might need to use multiple IF statements in your calculated column. I actually posted a tutorial on this topic last week & included the formulas used in the tutorial in the video description. Here is the link: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-bz1qSexNW9Y.html - Let me know if this helps. Thanks for watching!

@jackemanalo7741 2 года назад

@@LuiIacobellis Hello thanks for quick response. I actually tried that one too but disnt work or perhaps im doing it wrong. Basically i have 5 columns each columns have sub categories and the 6th column the result should be either a pass or a fail.

@LuiIacobellis 2 года назад

Feel free to email me at liacobellis01@gmail.com with some screenshots and more details and I'll see if I can help.

@jackemanalo7741 2 года назад

@@LuiIacobellis thank you just sent you an email

@LuiIacobellis 2 года назад

@@jackemanalo7741 Just sent over a formula that should work!