Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Find Date of Monday previous to Today

1. ## Find Date of Monday previous to Today

We have an excel report which looks at active orders and arranges them as a timeline according to the date in which they are due to be completed. The timeline shows all jobs due to be completed in a "week commencing" which SHOULD always begin on a Monday.

I am able to show weeks from today by inserting '=today()' in H5, then by using '=h5+7' to get following week commencing date.

The problem with this is that i have to run the report on Monday for all the Week commencing dates to be mondays.

How can i find the date of the Monday previous to today?

Hope that makes sense!

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,477

## Re: Find Date of Monday previous to Today

Hi,

Try,

=H5-WEEKDAY(H5,2)+1

HTH

3. ## Re: Find Date of Monday previous to Today

Hi Liz

consider

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

Wigi

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Find Date of Monday previous to Today

Thankyou very much - both versions work!

Don't suppose anyone could explain the logic of the string just so that i understand better for another time?

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Find Date of Monday previous to Today

Risking that Kris is typing at the same moment as I am now, still an answer:

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

gives the date of last Sunday. It is the day of today, minus 1 if we are on a Monday, 2 if on a Tuesday, ..., till 7 for a Sunday. The 2 as the last argument refers to whether weeks start on a Monday or a Sunday.

Knowing the date of Sunday, we do +1 and have the date for Monday.

OK now?

Wigi

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Find Date of Monday previous to Today

Thankyou very much, it's a bit like being back at school - it's nice to be given the answer but I'll only learn if I understand the reasoning!

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Find Date of Monday previous to Today

That's the correct way to do it indeed.

Good luck with your Excel work.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno