Formula to get the start or end of the week date

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 do a 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 reports, you need the boundaries of the week. So the first and the last day of the week. Now the formula is not quite straightforward as one might assume. 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. For calculating the start and end dates of the current week, this is the formula.

Now the formula is not quite straightforward as one might assume. 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. For calculating the start and end dates of the current week, this is the formula.

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

Be aware of 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

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. Also for the last day of the week use

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

Find out 60+ essential Excel Formulae here, to increase your comfort working with Excel leading to quick turn-around time.

Happy Excelling

Team Excelgoodies