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.

<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>
[TD="width: 75"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl76, width: 172, colspan: 3"]Current Fomula[/TD]
[TD="class: xl70, width: 89"][/TD]
[TD="class: xl70, width: 89"][/TD]
[TD="class: xl70, width: 111"][/TD]

[TD="class: xl66, colspan: 2"]Date For Formula[/TD]
[TD="class: xl69"]Start Date[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"]End Date
[/TD]
[TD="class: xl72"] Start Day
[/TD]
[TD="class: xl72"]
[/TD]
[TD="class: xl72"] End Day
[/TD]

[TD="class: xl65, align: right"]02/10/2018[/TD]

[TD="class: xl68, align: right"]27/09/2018[/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"]03/10/2018[/TD]
[TD="class: xl71, align: right"]Thursday[/TD]
[TD="class: xl71, align: right"][/TD]
[TD="class: xl71, align: right"]Wednesday[/TD]

[TD="class: xl65, align: right"]06/10/2018[/TD]

[TD="class: xl68, align: right"]04/10/2018[/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"]10/10/2018[/TD]
[TD="class: xl71, align: right"]Thursday[/TD]
[TD="class: xl71, align: right"][/TD]
[TD="class: xl71, align: right"]Wednesday[/TD]

[TD="class: xl65, align: right"]15/10/2018[/TD]

[TD="class: xl68, align: right"]11/10/2018[/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"]17/10/2018[/TD]
[TD="class: xl71, align: right"]Thursday[/TD]
[TD="class: xl71, align: right"][/TD]
[TD="class: xl71, align: right"]Wednesday[/TD]

[TD="class: xl65, align: right"]24/10/2018[/TD]

[TD="class: xl68, align: right"]18/10/2018[/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"]24/10/2018[/TD]
[TD="class: xl71, align: right"]Thursday[/TD]
[TD="class: xl71, align: right"][/TD]
[TD="class: xl71, align: right"]Wednesday[/TD]

[TD="class: xl65, align: right"]28/10/2018[/TD]

[TD="class: xl68, align: right"]25/10/2018[/TD]
[TD="class: xl68, align: right"][/TD]
[TD="class: xl68, align: right"]31/10/2018[/TD]
[TD="class: xl71, align: right"]Thursday[/TD]
[TD="class: xl71, align: right"][/TD]
[TD="class: xl71, align: right"]Wednesday[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]

[TD="class: xl76, colspan: 3"]Week Start Date Formula
[/TD]
[TD="class: xl70, colspan: 3"]=MIN(C3-WEEKDAY(C3,14)+1,EOMONTH(C3,0))

[/TD]

[TD="class: xl76, colspan: 3"]Week End Date Formula[/TD]
[TD="class: xl70, colspan: 3"]=MIN(C3+(7-WEEKDAY(C3,14)+0), EOMONTH(C3, 0))[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]

[TD="class: xl76, colspan: 3"]Required format for Formula

[/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]
[TD="class: xl70"][/TD]

[TD="class: xl69"]Start Date
[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"] End Date
[/TD]
[TD="class: xl74"] Start Day
[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl74"] End Day
[/TD]

[TD="class: xl73, align: right"]01/10/2018[/TD]
[TD="class: xl73, align: right"][/TD]
[TD="class: xl73, align: right"] 03/10/2018
[/TD]
[TD="class: xl75, align: right"]Monday[/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]Wednesday[/TD]

[TD="class: xl73, align: right"]04/10/2018[/TD]
[TD="class: xl73, align: right"][/TD]
[TD="class: xl73, align: right"]10/10/2018[/TD]
[TD="class: xl75, align: right"]Thursday[/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]Wednesday[/TD]

[TD="class: xl73, align: right"]11/10/2018[/TD]
[TD="class: xl73, align: right"][/TD]
[TD="class: xl73, align: right"]17/10/2018[/TD]
[TD="class: xl75, align: right"]Thursday[/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]Wednesday[/TD]

[TD="class: xl73, align: right"]18/10/2018[/TD]
[TD="class: xl73, align: right"][/TD]
[TD="class: xl73, align: right"]24/10/2018[/TD]
[TD="class: xl75, align: right"]Thursday[/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]Wednesday[/TD]

[TD="class: xl73, align: right"]25/10/2018[/TD]
[TD="class: xl73, align: right"][/TD]
[TD="class: xl73, align: right"]31/10/2018[/TD]
[TD="class: xl75, align: right"]Thursday[/TD]
[TD="class: xl75, align: right"][/TD]
[TD="class: xl75, align: right"]Wednesday[/TD]

</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.