Excel Snippets: Get First and Last Dates of Calendar Year

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)

Read original post Excel Snippets: Get First and Last Dates of Calendar Year at azurecurve|Ramblings of an IT Professional