Find Week start and End Date from any sepecific date of week required in Excel and MS Access.

[ashfaqahmedr]

Hi Everyone,

I want week start date (Thursday) and week end date (Wednesday) from current selected month, I have tried to find First date of week by this formula
"=MIN(C3-WEEKDAY(C3,14)+1,EOMONTH(C3,0))" and Week End Date by Formula "=MIN(C3+(7-WEEKDAY(C3,14)+0), EOMONTH(C3, 0))".

Formula to find last date of week work fine, but I found problem in week start date. I want to make 1st date of week as start of new week instead of going back to last month first date of week.



Here is all formulas with detail.

		Current Fomula
Date For Formula	Start Date		End Date	Start Day		End Day
02/10/2018		27/09/2018		03/10/2018	Thursday		Wednesday
06/10/2018		04/10/2018		10/10/2018	Thursday		Wednesday
15/10/2018		11/10/2018		17/10/2018	Thursday		Wednesday
24/10/2018		18/10/2018		24/10/2018	Thursday		Wednesday
28/10/2018		25/10/2018		31/10/2018	Thursday		Wednesday
		Week Start Date Formula	=MIN(C3-WEEKDAY(C3,14)+1,EOMONTH(C3,0))
		Week End Date Formula	=MIN(C3+(7-WEEKDAY(C3,14)+0), EOMONTH(C3, 0))
		Required format for Formula
		Start Date		End Date	Start Day		End Day
		01/10/2018		03/10/2018	Monday		Wednesday
		04/10/2018		10/10/2018	Thursday		Wednesday
		11/10/2018		17/10/2018	Thursday		Wednesday
		18/10/2018		24/10/2018	Thursday		Wednesday
		25/10/2018		31/10/2018	Thursday		Wednesday

<colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> </colgroup><tbody>
</tbody>

 

[lrobbo314]

Week Start Formula: =(Date)-(WEEKDAY(Date,15))
Week End Formula: =WeekStart +6

 

[ashfaqahmedr]

Week Start Formula: =(Date)-(WEEKDAY(Date,15))
Week End Formula: =WeekStart +6

This formula is nice but display last month start of week, I need same but only in current month which is specified in date field.