svickers423

New Member
Joined
Aug 23, 2015
Messages
3
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.

DateDay Num of WeekDay Date NumWeekMonthYearStart of week dateEnd of week date
1/27/2019127512019
1/28/2019228512019
1/29/2019329512019
1/30/2019430512019
1/31/2019531512019
2/1/201961522019
2/2/201972522019
2/3/201913622019
2/4/201924622019

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thanks in advance for any assistance!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top