How to Get the Last Day of the Week in a Month in Excel

The formulas described here can be used to create a dynamic table that will automatically populate with the dates of the last days of the week in a month. For example, the last Sunday of June (Youth Day), October (Daylight Saving Time ends), or March (Daylight Saving Time begins) or December the last Month in Year, etc.



Calculating the Last Day of the Week in a Month in Excel

The table below shows how to calculate the dates of the last days of the week in each month. Cell C3 contains the following formula:

=DATE($A3,$B3+1,1)-WEEKDAY(DATE($A3,$B3+1,C$1),1)
Calculating the last day of the week.

Download an example on how to find the last day of the month in Excel download file

This formula performs basic mathematical operations to calculate the required date based on the specified year and month.

To use this table, fill in the first two columns starting from cells A3 and B3 – year and month number – respectively.

Read also: How to Quickly Create a Calendar in Excel Using One Formula.

This dynamic table can be used in your Excel data models as a date generator that you can reference for use in your calculations.