Days of the week based on Today()

[Akashwani]

Hi,

I'm trying to work out how to have the dates of the week listed based on the current date. So in A1 I will have =today() in B1:B5 I would like to have the dates of this week, monday-friday.

A1 31/01/11

B1 28/03/11
B2 29/03/11
B3 30/03/11
B4 31/03/11
B5 01/04/11

I have played around with WEEKNUM, WORKDAY and WEEKDAY but I'm tying myself up in knots as usual.

Thanks

Ak

 

[VoG]

Hi Ak. Maybe like this

Excel Workbook
	A	B
1	31/03/2011	28/03/2011
2		29/03/2011
3		30/03/2011
4		31/03/2011
5		01/04/2011
Sheet2

 

[Asator]

If A1 has the date:

A2: =A1-WEEKDAY(A1)+2
A3: =A1-WEEKDAY(A1)+3
A4: =A1-WEEKDAY(A1)+4
A5: =A1-WEEKDAY(A1)+5
A6: =A1-WEEKDAY(A1)+6

 

[Zakkaroo]

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

This will return the Saturday of the 'current' week (assuming saturday is the start of your week)

B2 = B1+1 or = A1-WEEKDAY(A1,1)+3
etc

[Edit] Didnt' read the bit that you wanted it Mon-Fri so changed code slightly, but looks like already solved anyway!

 

[T. Valko]

Hi,

I'm trying to work out how to have the dates of the week listed based on the current date. So in A1 I will have =today() in B1:B5 I would like to have the dates of this week, monday-friday.

A1 31/01/11

B1 28/03/11
B2 29/03/11
B3 30/03/11
B4 31/03/11
B5 01/04/11

I have played around with WEEKNUM, WORKDAY and WEEKDAY but I'm tying myself up in knots as usual.

Thanks

Ak

Try this...

Entered in B1 and copied down to B5:

=A$1-WEEKDAY(NOW(),3)+ROWS(B$1:B1)-1

 

[Akashwani]

Hi,

WOW.

What can I say?

Thank you all so very much for your solutions, all do exactly as required and as I expected, multiple answers to what I initially thought was going to be an easy problem for me to solve, I still have so much to learn.

Thanks again.

Ak

Why do I spend hours banging my head and then decide to come here for the solution? :rofl

 

[Asator]

Banging your head against the wall is a useful learning technique.. I know I learned a lot that way