Getting the date for the start of the week

**JuzMe** · 06-04-2008, 04:35 AM

I have this column with a value like 04/06/2008. I will want another of the column to auto generate the date for the start of the week like 02/06/2008 which is a Monday.

How am I suppose to do this?

**Andy Pope** · 06-04-2008, 04:54 AM

=a1-weekday(a1,3)

**JuzMe** · 06-04-2008, 05:08 AM

Hmm but that does not seem to solve the problem to get the monday date of the week I keyed in the column.

For instance,

Column A: Column B:

24/06/2008 22/06/2008
25/06/2008 22/06/2008
26/06/2008 22/06/2008
27/06/2008 22/06/2008
29/06/2008 29/06/2008
30/06/2008 29/06/2008
01/07/2008 29/06/2008

Column A is the Date entered and Column B is the Date autogenerated when Column A is keyed in.

How am I suppose to write the formula for column B?

**Andy Pope** · 06-04-2008, 05:42 AM

See attached, which includes your data and my formula in column C

**JuzMe** · 06-04-2008, 06:13 AM

May I ask the meaning of this formula?

=A6-WEEKDAY(A6,3)

I think I need change the formula to cater the start of week on Sundays instead of Mondays

**Andy Pope** · 06-04-2008, 06:16 AM

Direct from the help

Return_type Number returned
1 or omitted Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2 Numbers 1 (Monday) through 7 (Sunday).
3 Numbers 0 (Monday) through 6 (Sunday).

My formula gives you the Monday of that week, which is what you asked for.

**Andy Pope** · 06-04-2008, 06:21 AM

There maybe other formulas

=IF(WEEKDAY(A1,3)=6,A1,A1-WEEKDAY(A1,3)-1)

**JuzMe** · 06-04-2008, 06:28 AM

Yr solutions solves 90% of my problem but there is a minor problem now.

However, what I want now is Sunday instead of Monday of the week.

This formula does not seem to apply if the date lands on Sunday itself, it returns back the previous week's Sunday after i change the formula to =A1-WEEKDAY(A1,2) for instance.

What am I suppose to do in order to let the formula stick to not moving to previous sunday but stick to this same date if it land on sunday?

**Andy Pope** · 06-04-2008, 06:34 AM

did you try the updated formula I posted?

**JuzMe** · 06-04-2008, 06:37 AM

I just used it and IT WORKS!! THNX SO MUCH!

**JuzMe** · 06-05-2008, 12:15 AM

Sry but I have another problem now.

When the date is 01/01/2008, the formula provided generate out 30/12/2007. Is it possible for the first week of the year to take on the first day of the year?

For instance, instead of 30/12/2007, I want it to generate 01/01/2008 instead. This only applies to the first week of the year only.

**Andy Pope** · 06-05-2008, 04:28 AM

Does this work for you?

=IF(WEEKNUM(A1)=1,DATE(YEAR(A1),1,1 ),IF(WEEKDAY(A1,3)=6,A1,A1-WEEKDAY(A1,3)-1))

**JuzMe** · 06-05-2008, 04:34 AM

There seem to have an error with it

**Andy Pope** · 06-05-2008, 04:58 AM

If it returns name then WEEKNUM is part of the analysis pak and you would need to load that addin.

**JuzMe** · 06-05-2008, 05:10 AM

Is there anyway instead of this formula then? Coz my this excel file will be shared and I do not want everyone to add this just for the sake of it.

**Andy Pope** · 06-05-2008, 05:17 AM

Try searching the forum for WEEKNUM and see is anybody has posted an alternative.

Or you could make the formula more complicated by check the month and day of the date to see if it is January and less than 6 days.