First day – Last day in Excel

Last Updated on 10/06/2023
Reading time: 2 minutes

This page will present formulas to calculate the first day, last day of the month, quarter, or year.

TODAY has been used in all the examples, but you can replace it with your date value.

How to build a custom date

The technique to calculate custom dates has been detailed in this article.

But also, you will need to understand how the DATE function always returns a correct date.

First day - Last day of the month

These calculations are straightforward to do with the DATE function.

First day of the month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

Last day of the month

In Excel, 1 is one day and not one hour. So using this rule, we have to subtract 1 from the formula that calculates the first day of the next month.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

Or you can use the function EOMONTH

=EOMONTH(TODAY(),0)

First Monday of the month

=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+6)

Last Monday of the month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,6))

Formulae for the first day of the month

Result in a workbook

The following workbook shows the different results according to the date of TODAY

First day - Last day of the year

You can find other examples of the first day or the last day in this article.

First day of the year

=DATE(YEAR(TODAY()),1,1)

Last day of the year

=DATE(YEAR(TODAY())+1,1,0)

or

=DATE(YEAR(TODAY()),12,31)

First Monday of the year

=DATE(YEAR(TODAY()),1,8)-WEEKDAY(DATE(YEAR(TODAY()),1,6))

Last Monday of the year

=DATE(YEAR(TODAY())+1,1,0)-WEEKDAY(DATE(YEAR(TODAY()),1,6))

Formulae for the first day of the year

Current, Previous or Next Quarter

For the calculation of a specific date of a quarter, the formulae are in this article.

First date of the current quarter

Number of days in the month

An essential and often ignored information is the number of days in a month. For monthly reports, this information is very important.

The following formulas allow you to return the number of days in a month.

Number of days in the current month

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)

Number of days in the next month

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)-1)

Number of days in the previous month

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1)

11 Comments

  1. Kunjal
    17/06/2022 @ 13:38

    please help me

    if today 2nd day of current month is monday and 1st date of current month is Sunday then return last month end date in excel.

    Example if today 02-05-2022 is Monday then return last month end date (30-04-2022)

    please help me

    Reply

  2. Rick Rothstein
    13/11/2021 @ 03:59

    Here is another way to calculate the first day of the month...

    =TODAY()-DAY(TODAY())+1

    Reply

  3. Bill Savage
    30/07/2021 @ 17:10

    This is great! Thank you.

    I have tried - unsuccessfully - to have Excel give me the first day and date of each month for future years to plan events. A check list, if you will. I can use the vlookup function with a list of all the dates in a year but am using a calendar with one month per sheet.

    I use a preformatted calendar for the following years and modify it to highlight the current date - the day matching today() - as well as the current date in the prior or following month, as well as some other items. E.g. highlighting the day when a particular event occurs or person is working.

    Reply

  4. Subhajit Gorai
    27/05/2021 @ 13:31

    I want to get last day of the financial year from a date. Example- if input is 10/05/2021...output will be 31/03/2022

    Reply

  5. Pramod
    20/12/2020 @ 18:46

    Thank you, Your post has helped me on a project i am working on.

    Reply

    • Frédéric LE GUEN
      20/12/2020 @ 20:05

      🙂

      Reply

  6. Kevin PhD
    09/12/2020 @ 23:49

    Thank You. First and Last Day of the Year Formulas are greatly appreciated.
    So great to find help when needed.

    Reply

  7. Mac McCorison
    30/08/2019 @ 17:16

    Thank you for publishing these. It helped with a spreadsheet I'm working on. I did discover an error in a couple of the formulas though. The YEAR function is missing a closing parenthesis in the first day of the year and last day of the year examples.

    Reply

    • Frédéric LE GUEN
      31/08/2019 @ 03:34

      Thanks

      Reply

  8. David Stein
    30/08/2019 @ 14:37

    Nice blog post. I found a typo that you may want to fix. First day of year - You are missing an end parenthesis. It should read: =DATE(YEAR(TODAY()),1,1)

    Reply

    • Frédéric LE GUEN
      31/08/2019 @ 03:33

      Thanks 🙂

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

First day – Last day in Excel

Reading time: 2 minutes
Last Updated on 10/06/2023

This page will present formulas to calculate the first day, last day of the month, quarter, or year.

TODAY has been used in all the examples, but you can replace it with your date value.

How to build a custom date

The technique to calculate custom dates has been detailed in this article.

But also, you will need to understand how the DATE function always returns a correct date.

First day - Last day of the month

These calculations are straightforward to do with the DATE function.

First day of the month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

Last day of the month

In Excel, 1 is one day and not one hour. So using this rule, we have to subtract 1 from the formula that calculates the first day of the next month.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

Or you can use the function EOMONTH

=EOMONTH(TODAY(),0)

First Monday of the month

=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+6)

Last Monday of the month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,6))

Formulae for the first day of the month

Result in a workbook

The following workbook shows the different results according to the date of TODAY

First day - Last day of the year

You can find other examples of the first day or the last day in this article.

First day of the year

=DATE(YEAR(TODAY()),1,1)

Last day of the year

=DATE(YEAR(TODAY())+1,1,0)

or

=DATE(YEAR(TODAY()),12,31)

First Monday of the year

=DATE(YEAR(TODAY()),1,8)-WEEKDAY(DATE(YEAR(TODAY()),1,6))

Last Monday of the year

=DATE(YEAR(TODAY())+1,1,0)-WEEKDAY(DATE(YEAR(TODAY()),1,6))

Formulae for the first day of the year

Current, Previous or Next Quarter

For the calculation of a specific date of a quarter, the formulae are in this article.

First date of the current quarter

Number of days in the month

An essential and often ignored information is the number of days in a month. For monthly reports, this information is very important.

The following formulas allow you to return the number of days in a month.

Number of days in the current month

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)

Number of days in the next month

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)-1)

Number of days in the previous month

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1)

11 Comments

  1. Kunjal
    17/06/2022 @ 13:38

    please help me

    if today 2nd day of current month is monday and 1st date of current month is Sunday then return last month end date in excel.

    Example if today 02-05-2022 is Monday then return last month end date (30-04-2022)

    please help me

    Reply

  2. Rick Rothstein
    13/11/2021 @ 03:59

    Here is another way to calculate the first day of the month...

    =TODAY()-DAY(TODAY())+1

    Reply

  3. Bill Savage
    30/07/2021 @ 17:10

    This is great! Thank you.

    I have tried - unsuccessfully - to have Excel give me the first day and date of each month for future years to plan events. A check list, if you will. I can use the vlookup function with a list of all the dates in a year but am using a calendar with one month per sheet.

    I use a preformatted calendar for the following years and modify it to highlight the current date - the day matching today() - as well as the current date in the prior or following month, as well as some other items. E.g. highlighting the day when a particular event occurs or person is working.

    Reply

  4. Subhajit Gorai
    27/05/2021 @ 13:31

    I want to get last day of the financial year from a date. Example- if input is 10/05/2021...output will be 31/03/2022

    Reply

  5. Pramod
    20/12/2020 @ 18:46

    Thank you, Your post has helped me on a project i am working on.

    Reply

    • Frédéric LE GUEN
      20/12/2020 @ 20:05

      🙂

      Reply

  6. Kevin PhD
    09/12/2020 @ 23:49

    Thank You. First and Last Day of the Year Formulas are greatly appreciated.
    So great to find help when needed.

    Reply

  7. Mac McCorison
    30/08/2019 @ 17:16

    Thank you for publishing these. It helped with a spreadsheet I'm working on. I did discover an error in a couple of the formulas though. The YEAR function is missing a closing parenthesis in the first day of the year and last day of the year examples.

    Reply

    • Frédéric LE GUEN
      31/08/2019 @ 03:34

      Thanks

      Reply

  8. David Stein
    30/08/2019 @ 14:37

    Nice blog post. I found a typo that you may want to fix. First day of year - You are missing an end parenthesis. It should read: =DATE(YEAR(TODAY()),1,1)

    Reply

    • Frédéric LE GUEN
      31/08/2019 @ 03:33

      Thanks 🙂

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *