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. 


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. 

Popular posts from this blog

Access Web App | Filter a view by current user login (deprecated)

Update (April 1 2017): Microsoft has released an update that willl stop the creation of new apps starting June 2017, and shut down any remaining apps by April 2018. See more here

The problem One of the things I needed to do lately was see if a view within Access Web Apps can be configured to filter data that is related to the logged in user. e.g. as a user I want to be able to view a list of projects where I had been assigned as the project owner.

In SharePoint, this would have been quite easy to achieve as we can create a view and set the filter to {column name} is equal to [me].

Unfortunately Access Web Apps does not quite work this way:

No people picker control in Access Web Apps - this means we need to store and manage our own list of user data within a table in your Access Web App Database (or find a way to link a table with SharePoint's user profile or hidden user information list).Access Web Apps is a relational database - this means the approach to filtering a table view is…

Nintex Workflow | parsing JSON responses from json-only web requests

Update 03/08/16:
Logic Apps has gone GA, and has undergone a major v2 change since this post was written. For the most part, the core actions remain the same but just renamed or work a little differently e.g. http listener (now known as "When an http request is received" + "Received") and conditions (now triggered via the add a condition" button instead of being configured inside the http action.)

It is worth noting that the BizTalk JSON Encoder API app can no longer be found in the marketplace. This is now a native function in Logic Apps. Though, I'll try to refresh this post with the how to do it in the version of Logic Apps that GA'd, I'm not sure when I will have to do it, so if you can't wait, I suggest looking at the xml function can be found here.

Update 07/08/16:
Steps for new Logic App UI that has GA have been added into the solution section below.
The problem
One of the things I have been working on lately in the Nintex Workflow world,…

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 leaveRequires 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 for business users to create…