With
E10: (a date)
E11: =E10-WEEKDAY(E10,2)+1
WEEKDAY(E10,2) returns the weekday_number of the date in E10
where: Mon = 1, Tue = 2, Wed = 3, Thu = 4, Fri = 5, Sat = 6, Sun = 7
So...If you subtract a date's weekday_num from the date,
the result is the Sunday date prior to that date.
Adding 1 to that value is the Monday date for the week containing the date in E10.
Example:
E10: 07/15/2008.....(a Tuesday)
• WEEKDAY(E10,2)....returns: 2
• =E10-WEEKDAY(E10,2)
=07/15/2008 - 2 days
=07/13/2008......(a Sunday)
• =E10-WEEKDAY(E10,2)+1
=07/15/2008 - 2 days + 1 day
=07/14/2008......(a Monday)
Note: You can designate which day you want the week to start on
in the WEEKDAY function by setting the 2nd argument value.
See Excel Help on that function for the details.
I hope that helps.
Bookmarks