< Go Back

Get first Monday before any date

You can find first Monday in your excel sheet data using a simple formula. If you have a set of data and have a random date selected. If you want to select the nearest Monday in time you can choose the following formula:

=date-WEEKDAY(date-2)

  EXAMPLE

Figure 1. Example of get the first Monday before any date.

The formula used in the B2 is

=A2-WEEKDAY(A2-2)

This formula is based on the WEEKDAY function.  

Working of the formula:

If you desire to find the last Monday from a randomly selected date, you will have to roll back the specific number of days depending on the day of your selected date. For example, if the day on your selected date is Monday then you will have to roll back 7 days. If your selected date is Tuesday then you will have to roll back one day, if it is Wednesday you will have to roll back two days and so on:

The table below shows the number of days you have to roll back to find last Monday:

 

Day

No of days to roll back

Monday

7

Tuesday

1

Wednesday

2

Thursday

3

Friday

4

Saturday

5

Sunday

6

 

In MS excel the number selected for Monday is 2 it is because the first day of the week in excel is Sunday whose number is 1. Based on this data of the excel, if the randomly selected date is Thursday, Jun 4, then we know that from Thursday, you will have to roll back 3 days so by minimizing 3 days from Jun 4, the resulted date will be Jun 1 which will be the last Monday from our selected date.

If this function is complex for you, you can also use the following formula which is dependent on the CHOOSE function:

=A2-CHOOSE(WEEKDAY(A2),6,7,1,2,3,4,5)

Selecting Monday from the current date:

Youkan also select the last Monday form the current date or the date of today. For this, you can use the TODAY function. The formula is as below;

“=TODAY ()-WEEKDAY (TODAY ()-2)”

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar