Skip to main content

SharePoint | Set default value of date field to last day of the month


One of the things, that I needed to do recently is find a way to set the default value of a date field to the last day of the month.

Though some content were really helpful in getting me on the way to solving the problem, I thought I'd post the formula required to make it easier for others looking to do the same in the future.

What I wanted to do 

The steps below describe what I wanted to achieve:
  1. User uploads a file into a document library
  2. A date column will automatically be populated with the date representing the end of the month (calculated based on the date the file was uploaded)

How do we do this?


  1. Open your column settings for your date field
  2. Under the Additional Column Settings, set the default value option to Calculated Value
  3. Enter the the formula below:

    =(DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY("1-Apr-2008"))) - (1)
  4. Click save and that's all you need to do

What does the formula above do?

The formula above is quite simple.

  • =(DATE(YEAR... | The date date function to allow you to set the value to a date value. e.g. you can manually set the date to 31 July 2016 by entering the function =DATE(2016,7,31)
  • ...DATE(YEAR(TODAY()),MONTH... | I then set the year to the current date's year
  • ...()),MONTH(TODAY())+1,DAY... | I then set the month to the current date's month and add one month to it
  • ...())+1,DAY("1-Apr-2008")... | I then set the day to 1 to represent the 1st day of the month. Note that I was having a few issues when I used just 1, so I hard coded a random date value. 
  • ...008"))) - (1)  | The calculation above should give me the 1st day of next month. I deduct one day to get the last day of the current month. 

Conclusion

I haven't tested on a leap year and on Februaries, so I'm not sure how well it works on special dates. Have you implemented something similar and can recommend a better way to approach this particular problem? 

I would be happy to hear your thoughts in the comments below. 
Labels:

Popular posts from this blog

An intro to conditional validation and formatting in PowerApps (using SharePoint as a data source)

The Problem What if we need to create a form that changes it's behaviour based on the answer entered by the user. For example, we need to build a leave request form that: Shows the comment field if applying for a sick leave, but hides the comment field if applying for a annual leave Requires the requestor to write a comment if their sick leave is more than 1 day, but is optional if sick for just 1 day.  The default form interface for custom lists in SharePoint Online can be used for basic scenarios, but doesn't provide an easy way to add business logic described above ( unless you want to write some javascript ) In the past InfoPath would have been the tool of choice for many, however this tool is now in maintenance mode and whilst it will still be supported for a wee while  there will be no improvements or enhancements to the platform. The Solution This is where PowerApps come in. PowerApps is now the platform that is recommended by Microsoft as a way fo
Read more

Only show a control to an employee's manager in PowerApps (using SharePoint as a data source)

The Problem In my previous blog post , I wrote a basic tutorial on configuring PowerApps to conditionally show/hide and make mandatory/optional a control based on the answer provided in a previous control. In this blog post we will expand on that scenario to create a form that only shows a control called approval status to a user if that logged in user is the manager of the user specified in the employee control. This means when the manager is using the application, they can change the status to approved. Meanwhile, a user that is not a manager of the person specified in the employee cannot change the status to approved. The Solution Aside from containing a variety of native functions, PowerApps also allow us to add connections to add functionalities into our app. In this case we will add the Office 365 Users connection so that we can get information on: The user that is logged on The manager of the user specified in the employee control Note: Whilst this soluti
Read more

Uploading a file from a SharePoint document library into an Azure Blob Storage with Microsoft Flow (or Logic App)

The Problem One of the things that I've been experimenting with lately is Microsoft Flow . The service allows you to build process automation to facilitate transfer of information from one system to another easily. One scenario I wanted to try out is to be able to copy/move a file from SharePoint to an Azure Blob Storage. There are a number of reasons that you may want to do this: Archive files that are no longer needed Upload a copy of a file (usually an image) into a Blob Storage so that images can be hosted in a CDN to optimize page load performance In the past doing something like this would have required some form of custom development e.g. a remote event receiver. However the introduction of Microsoft Flow and Logic Apps has created another alternative that is worth exploring.... Update 30/09/16:  If you are looking to put your assets into a CDN to make it load faster, then it's worth checking out the new Preview Release  Office 365 Public Content Delivery
Read more