🔔SIGN UP FOR MY SHAREPOINT LIST FUNDAMENTALS COURSE🔔 bit.ly/461F4iX ❓Looking for support with an issue or to get a response to a question - submit it here: asqme.com/@LuiIacobellis
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!
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?
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
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...
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?
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.
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!
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?
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
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!
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
Is there a way to calculate how many years have passed? For example, the column's title is "Date Completion", and I want another column to show how many years ago it was completed.
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.
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
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.
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: ruclips.net/video/bz1qSexNW9Y/видео.html . Hope this helps and thank you for watching
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
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!
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?
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: ruclips.net/video/bz1qSexNW9Y/видео.html - hope this helps
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
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
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
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!
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.
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
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.
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!
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)]
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!
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!
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
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
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
Hi there, I would suggest you post this question with more detail over on the Microsoft Tech Community: techcommunity.microsoft.com/ - Hope this helps!
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
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!
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
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!
@@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
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😭
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: ruclips.net/video/bz1qSexNW9Y/видео.html - Let me know if this helps. Thanks for watching!
@@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.
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!
🔔SIGN UP FOR MY SHAREPOINT LIST FUNDAMENTALS COURSE🔔 bit.ly/461F4iX
❓Looking for support with an issue or to get a response to a question - submit it here: asqme.com/@LuiIacobellis
Thanks Luigi, it solved my issue after watching your video! Kudos to you.
You're very welcome and thanks for watching
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!
Loved your video! it helped me for a very important tracking report!!! thank you so much!
@andrezocco You're very welcome! Thanks for watching!
Thank you for sharing to resolve my problems❤
You're very welcome! Glad it was helpful.
Lui, just ran across you videos. I like your style. Subscribed!
Hi Phil, thanks for the kind words and for subscribing. Talk soon!
Your videos are really informative and effective, keep going
Thanks Rahul! I'm glad you find them helpful!
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?
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
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...
Thank you very much Lui!
@JorgeEmilioVázquezMoran You're very welcome! Thanks for watching!
CHECK OUT MY HOW TO USE SHAREPOINT PLAYLIST - 40+ TUTORIALS: ruclips.net/p/PLmE7KGV9-I4uibXaJ7ZqTXbbS4tukRJPh
Hi Lui, brilliant video. How would you write a formula to generate a column that is + 1 year from the date in another column?
Hi Jordan, thanks for the comment. Your formula would be the date + 365 to add in the additional time. Hope this helps!
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?
Is it possible to have two formulas embedded in the sharepoint list? The first formula is TODAY()-Creation Date
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.
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!
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?
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
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!
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
Thanks Lui, great video. One question. Is there a way to calculate the week of the year, from a date in another column?
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
Is there a way to calculate how many years have passed? For example, the column's title is "Date Completion", and I want another column to show how many years ago it was completed.
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.
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
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.
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: ruclips.net/video/bz1qSexNW9Y/видео.html . Hope this helps and thank you for watching
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
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!
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?
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: ruclips.net/video/bz1qSexNW9Y/видео.html
- hope this helps
good video, thanks.
Moises Tatis You're very welcome! Thanks for watching!
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
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
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
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!
Hi Lui, is it possible to calculate the total number of seconds spent on a specific work in Sharepoint?
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.
@@LuiIacobellis thank you. Been trying to figure out the formula and you finally answered the question. Bummer that its not possible. 😔
Can i calculate ISO string from date?
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
Good one
Thank you! Cheers!
Hello thanks for the video, Is it possible to set "date and time" by the end date of each month?
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.
Hi sir,
I need a calculation to find week number in SharePoint list pls give me advise.
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!
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)]
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!
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!
Unfortunately I can't provide this type of support via comments. I'd suggest you post your question over on the Microsoft Tech Community
Unfortunately I can't provide this type of support. I'd suggest you post your question over on the Microsoft Tech Community
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
Thank you for catching this error! Much appreciated
All the dates are in UTC timezone. How to change that in Current/Local timezone?
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
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
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
ask sir, how to calculate the age of the invoice based on working days?
Hi there, I would suggest you post this question with more detail over on the Microsoft Tech Community: techcommunity.microsoft.com/ - Hope this helps!
Are you able to hide date field until a column value is selected?
This video covers conditional hide show of field - ruclips.net/video/V9ysKQRMJ00/видео.html - it might help address your question
How we can do calculated column based on start date and end date
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
How to use networkdays btw two dates
Hi there, I've added this to my backlog & will be publishing a tutorial on this topic in the coming weeks. Thanks for watching!
How to calculate no of days on below conditions
I have open date as yesterday
But do not have close date?
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!
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
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!
@@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
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😭
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: ruclips.net/video/bz1qSexNW9Y/видео.html - Let me know if this helps. Thanks for watching!
@@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.
Feel free to email me at liacobellis01@gmail.com with some screenshots and more details and I'll see if I can help.
@@LuiIacobellis thank you just sent you an email
@@jackemanalo7741 Just sent over a formula that should work!
I need to add a column that auto populate a due date
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!