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:
- User uploads a file into a document library
- 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?
- Open your column settings for your date field
- Under the Additional Column Settings, set the default value option to Calculated Value
- Enter the the formula below:
=(DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY("1-Apr-2008"))) - (1)
- 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.