Excel Unplugged

Formula to get the Start or End Date of the week

On many occasions and in many dashboards, time will be the main factor of your analysis and reports. As far as time goes, you can create Yearly, Quarterly, Monthly, Weekly or Daily reports. For each and every one of those except for the Weekly reports, the dates are known and can be calculated or manually inserted. But for the Weekly report, you need the boundaries of the week. So the first (Start Date of the Week) and the last (End Date Of The Week) day of the week. Now the formula for the First and Last Date Of the Week is not quite straight forward as one might assume. Or to put it differently, whereas there is a formula for the week number there is no built in formula for calculating the Start and the End Date of the Week.

But that doesn’t stop you from writing your own.

If you find it easier to learn through video, you can watch the video that shows you which formula to use and explains why it works just click here or watch the video embedded at the end of this blog post.

Start Date Of the Week Formula

For calculating the Start date of the current week, this is the formula.

=TODAY()-WEEKDAY(TODAY())+1

Formula to get the start date or end date of the week

Watch out for the WEEKDAY function. If so required, add the second argument. By default this formula will return the last Sunday. If in your region week starts on Monday, change the formula to

=TODAY()-WEEKDAY(TODAY(),2)+1

End Date Of the Week Formula

If one wishes to take this to the last or next week, a simple addition of 7 or a desired multiple of seven will do.
So for the End Date of the Week use

=TODAY()-WEEKDAY(TODAY())+7

The formulas above will give you the First and the last Date of the Week. But it has to be said, Power Query has this functionality Built-In. If you want to use Power Query to get the First Date of the Week and the Last Date of The Week, you can read a post about it here. The video below also explains both methods to get the Start and End date of the Week.

Comments 5

  1. Krutika says:

    Hi I want to get weeks date range, from start and end date of month

    1. Not sure I understand. Could you elaborate further?

      1. Qadeer Ahmed says:

        He means
        Dates are in Column A
        He wants to group the dates as week start and Week end date in cells like
        B2= 4Jul2021 – 10Jul2021
        C2=11Jul2021 – 17July 2021
        D2= 18July2021 – 24July2021….

        Either in Columns or in a single row horizontally stretched a cross…

        1. In Power Query, you simply create those columns by Add Column/Date/Week/Start (End) of the week and then selecting both columns do a “Combine Columns” by adding the ” – ” as separator

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.