Formula to generate date of weekday (e.g. Monday) of given week

**rain4u** · 04-18-2011, 09:09 PM

Hi
Can someone please advise me. I need a formula that using the date stored in A1 calculates the date of given weeks Monday ( I need to have formulas for all 7 days of the week, but lets use Monday as an example)

I.e. if date in A1 is ---> then the outcome would be

18/04/2011 ---> 18/04/2011
19/04/2011 ---> 18/04/2011
20/04/2011 ---> 18/04/2011
21/04/2011 ---> 18/04/2011
22/04/2011 ---> 18/04/2011
23/04/2011 ---> 18/04/2011
24/04/2011 ---> 18/04/2011
25/04/2011 ---> 25/04/2011

Now this was with Monday but I need to have one formula for every day of the week (Mon, Tue, Wed, etc). Or alternatively if there can be one formula where I need to edit part of the code to make it happe that would be awesome.

Any ideas?

Cheers

**Ron Coderre** · 04-18-2011, 09:15 PM

EDITED TO CHANGE THE FORMULA TO ACCOMMODATE WEEKS BEGINNING ON A MONDAY
For a date in A1 and weeks beginning on a Monday,
This formula returns the Monday date of the week containing A1:

Please Login or Register  to view this content.

Does that help?

**rain4u** · 04-18-2011, 10:30 PM

=A1-WEEKDAY(A1,2)+1----- Monday, done!
=A1-WEEKDAY(A1,3)+1----- Tuesday, done!
=A1-WEEKDAY(A1,4)+1----- Wednesday, returns error #NUM!
=A1-WEEKDAY(A1,5)+1----- Thursday returns error #NUM!
=A1-WEEKDAY(A1,6)+1----- Friday, returns error #NUM!
=A1-WEEKDAY(A1,7)+1----- Saturday returns error #NUM!
=A1-WEEKDAY(A1,1)+1----- Sunday, returns Sundays date but previous its weeks previous weeks Sundays date. In most parts of Europe we consider current week from Mon to Sun, not Sun to Sat.

Any ideas how to get the remaining formulas that I'm missing i.e. from Wednesday to Sunday

Cheers
Rain

**rain4u** · 04-18-2011, 10:42 PM

I found the solution. I will post it here so maybe someone else can benefit from it as well
Formula is
=IF(WEEKDAY(A1)=1,A1,A1+(2-WEEKDAY(A1)))
Change the number in red to get the following dates of the weekday

Monday --- 2
Tuesday --- 3
Wednesday --- 4
Thursday --- 5
Friday --- 6
Saturday --- 7
Sunday --- 8

Cheers
Rain

**Ron Coderre** · 04-19-2011, 01:06 PM

Regarding:

Please Login or Register  to view this content.

You don't have to change the formula I posted for it to work correctly...
Just copy it down.

See the attached file.

**rain4u** · 04-19-2011, 01:28 PM

Originally Posted by rain4u

Hi
I need to have formulas for all 7 days of the week, but lets use Monday as an example

Originally Posted by rain4u

Now this was with Monday but I need to have one formula for every day of the week (Mon, Tue, Wed, etc). Or alternatively if there can be one formula where I need to edit part of the code to make it happen that would be awesome.

I think we misunderstood each other. I needed to have a formula that does exactly what your offered formula does. But I needed to have one for Tuesday, Wednesday, etc. So for tuesday i wanted to have This

I.e. if date in A1 is ---> then the outcome would be

18/04/2011 ---> 19/04/2011
19/04/2011 ---> 19/04/2011
20/04/2011 ---> 19/04/2011
21/04/2011 ---> 19/04/2011
22/04/2011 ---> 19/04/2011
23/04/2011 ---> 19/04/2011
24/04/2011 ---> 19/04/2011
25/04/2011 ---> 26/04/2011
26/04/2011 ---> 26/04/2011

But I already have this now.

Thank you for your help regardless.

Cheers
Rain

**Ron Coderre** · 04-19-2011, 03:01 PM

Got it...I understand now...and apologies for my being a bit dense

**daddylonglegs** · 04-19-2011, 03:30 PM

Originally Posted by rain4u

=IF(WEEKDAY(A1)=1,A1,A1+(2-WEEKDAY(A1)))

If you use this formula then it will always return a Sunday date if A1 is a Sunday......irrespective of the number in red - is that your intention?

**rain4u** · 04-19-2011, 07:50 PM

Yes that is the intention. But good to know.

**rain4u** · 04-19-2011, 07:53 PM

Originally Posted by Ron Coderre

Got it...I understand now...and apologies for my being a bit dense

No probs mate. My English is as it is. And sometimes its bit hard to get something out in very clear and simple way. You know exactly what you want but its hard to get that across to others.
At least you tried to help me and that is what counts.

Cheers