Partial Weeks

[svickers423]

I am trying to create a formula or script that will identify partial weeks. The end goal will be to yield if a month ends in the middle of the week I want to have the week start date be the same and the end of week be the last day of the month and the start of the next month be the first day of the week. Weeks start on Sunday and end on Saturday. My dates start in column A2 and go down the row-
In the below the end result would be in the 'Start of week column' would be for 1/27/2019 - the start of week would be 1/27/2019 and EOW would be 1/31/2019 and for 2/1/2019 the start of week would be 2/1/2019 and EOW would be 2/2/2019. All full weeks in the month would hold true to the Sunday date is the start and the EOW would be the Saturday date of that week.

[TABLE="width: 709"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Day Num of Week[/TD]
[TD]Day Date Num[/TD]
[TD]Week[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Start of week date[/TD]
[TD]End of week date[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/27/2019[/TD]
[TD]1[/TD]
[TD]27[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/28/2019[/TD]
[TD]2[/TD]
[TD]28[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/29/2019[/TD]
[TD]3[/TD]
[TD]29[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/30/2019[/TD]
[TD]4[/TD]
[TD]30[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2019[/TD]
[TD]5[/TD]
[TD]31[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2019[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/2/2019[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/3/2019[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2/4/2019[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any assistance!

 

[mrshl9898]

Try:

G2 =MAX(DATE(F2,E2,1),DATE(F2,E2,IF(B2=1,C2,C2-B2+1)))
H2 =MIN(EOMONTH(A2,0),DATE(F2,E2,IF(B2=7,C2,C2-B2+7)))

*Don't know if dd/mm/yyyy interferes

 

[svickers423]

Seems to work like a champ!!! Thanks for the assistance!