It doesn’t come up very often as I do a lot of work using SQL Server rather than Microsoft Excel, but every so often I do need to do some date manipulation in Excel.
I recently needed to calculate the first and last dates of a year from a user supplied date for a Jet Report. This can easily be done using a combination of the YEAR and DATE functions.
The YEAR function can be used to extract the year from a supplied date:
=YEAR(TODAY())
The date function is supplied three parameters: year, month and day.
The output of the YEAR function gives us the first parameter and for the month and day of the first day of the year we can simply supply the number 1:
=DATE(YEAR(TODAY()),1,1)
To get the last day of the year, the month parameter is set to 12 and the day to 31:
=DATE(YEAR(TODAY()),12,31)
Click to show/hide the Excel Snippets Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.