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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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

N
  • Solved
Formula For Entering Loan Repayment On The Same Day For Each Month
Replies
4
Views
400
Dave Patton
D
staticfluids
  • Solved
Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank
Replies
4
Views
492
staticfluids
staticfluids
I
  • Solved
Formula to count the week from the first weekday of the month
Replies
8
Views
446
InvaderZIM05
I
F
  • Solved
Date Range Period over Period over years
Replies
7
Views
675
flashgordie
F
J
  • Question Question
Calculating Week No of the Month
Replies
3
Views
331
jajatidev
J

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

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