46. Excel Tips - Calculate the 1st Day of the month

Nick's picture

This tip explains how to Calculate the 1st Day of the month in Excel.

We know how to get today's date... use: =TODAY()

For me right now, this returns 30-Jan-2009 ... so is there a way to get the 1st Day of the month from this ?

YES - use the DATE function.

DATE is a handy function that allows you to construct a date whilst inputting YEAR, MONTH and DAY. Excel also has the functions YEAR(), MONTH() and DAY() which take a date, and return the YEAR, MONTH and DAY respectively.

So lets see this in action.


- will return the 1st day of the month. - it takes the year and month from TODAY, and uses 1 as the day.



Download a spreadsheet to practice calculating the 1st day of the current month in Excel


Training video on how to Calculate the first day of the month:

1st day of the month

This formula is more direct:


Dennis Taylor