Formula to get start and end date of week (starting on 1st of month), without crossing over months

S

seaottr

Board Regular
Joined
Feb 10, 2010
Messages
60
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm sure this is an easy one, but I've scoured the internet for an answer and can't find one for my particular scenario.

I'm looking for a formula that will return the first and last day of the week with the first of the month being the start date and the first Saturday being the last day, without going into the next month.

For example:

January 1, 2022 should return "1/1/2022 - 1/1/2022"
January 2, 2022 should return "1/2/2022 - 1/8/2022"
...
January 30, 2022 should return "1/30/2022 - 1/31/2022"

Any help would be appreciated!

Thank you in advance!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Easier if you have a late version of excel.

MrExcelPlayground6.xlsx
DEF
15Saturday, January 1, 2022Saturday, January 1, 2022Saturday, January 1, 2022
16Sunday, January 2, 2022Sunday, January 2, 2022Saturday, January 8, 2022
17Monday, January 3, 2022Sunday, January 2, 2022Saturday, January 8, 2022
18Tuesday, January 4, 2022Sunday, January 2, 2022Saturday, January 8, 2022
19Wednesday, January 5, 2022Sunday, January 2, 2022Saturday, January 8, 2022
20Thursday, January 6, 2022Sunday, January 2, 2022Saturday, January 8, 2022
21Friday, January 7, 2022Sunday, January 2, 2022Saturday, January 8, 2022
22Saturday, January 8, 2022Sunday, January 2, 2022Saturday, January 8, 2022
23Sunday, January 9, 2022Sunday, January 9, 2022Saturday, January 15, 2022
24Monday, January 10, 2022Sunday, January 9, 2022Saturday, January 15, 2022
25Tuesday, January 11, 2022Sunday, January 9, 2022Saturday, January 15, 2022
26Wednesday, January 12, 2022Sunday, January 9, 2022Saturday, January 15, 2022
27Thursday, January 13, 2022Sunday, January 9, 2022Saturday, January 15, 2022
28Friday, January 14, 2022Sunday, January 9, 2022Saturday, January 15, 2022
29Saturday, January 15, 2022Sunday, January 9, 2022Saturday, January 15, 2022
30Sunday, January 16, 2022Sunday, January 16, 2022Saturday, January 22, 2022
31Monday, January 17, 2022Sunday, January 16, 2022Saturday, January 22, 2022
32Tuesday, January 18, 2022Sunday, January 16, 2022Saturday, January 22, 2022
33Wednesday, January 19, 2022Sunday, January 16, 2022Saturday, January 22, 2022
34Thursday, January 20, 2022Sunday, January 16, 2022Saturday, January 22, 2022
35Friday, January 21, 2022Sunday, January 16, 2022Saturday, January 22, 2022
36Saturday, January 22, 2022Sunday, January 16, 2022Saturday, January 22, 2022
37Sunday, January 23, 2022Sunday, January 23, 2022Saturday, January 29, 2022
38Monday, January 24, 2022Sunday, January 23, 2022Saturday, January 29, 2022
39Tuesday, January 25, 2022Sunday, January 23, 2022Saturday, January 29, 2022
40Wednesday, January 26, 2022Sunday, January 23, 2022Saturday, January 29, 2022
41Thursday, January 27, 2022Sunday, January 23, 2022Saturday, January 29, 2022
42Friday, January 28, 2022Sunday, January 23, 2022Saturday, January 29, 2022
43Saturday, January 29, 2022Sunday, January 23, 2022Saturday, January 29, 2022
44Sunday, January 30, 2022Sunday, January 30, 2022Monday, January 31, 2022
45Monday, January 31, 2022Sunday, January 30, 2022Monday, January 31, 2022
Sheet30
Cell Formulas
RangeFormula
E15:E45E15=IF(MONTH(INT((D15+6)/7)*7-6)=MONTH(D15),INT((D15+6)/7)*7-6,DATE(YEAR(D15),MONTH(D15),1))
F15:F45F15=IF(MONTH(INT((D15+6)/7)*7)=MONTH(D15),INT((D15+6)/7)*7,DATE(YEAR(D15+10),MONTH(D15+10),1)-1)
 
Upvote 0
Solution
What version of Excel do you have?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Thank you so much JamesCanale! Works perfectly! Really appreciate your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Date Range Period over Period over years
Replies
7
Views
462
flashgordie
F
J
  • Question
Calculating Week No of the Month
Replies
3
Views
80
jajatidev
J
W
  • Solved
HELP! SUMIFS/Logic/Dates Functions
2
Replies
10
Views
868
Alex Blakenburg
A
SaraWitch
  • Solved
Return first Monday in April from one date and first Monday in current year April if another is blank
2 3
Replies
28
Views
776
Dave Patton
D
S
  • Solved
FTE based on start & end date
Replies
8
Views
1K
s_1592
S

Forum statistics

Threads
1,214,426
Messages
6,119,414
Members
448,895
Latest member
omarahmed1

Share this page

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