Ranged date based on today's date

Michael Noze
Michael Noze used Ask the Experts™
on
Hi Experts,

I'm looking for a formula that gives a ranged date (from saturday to friday) based on today's date.

IE:
We're the 2019-11-21, so the ranged date would be: 16-11-2019 to 22-11-2019
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try

=A1-WEEKDAY(A1)
=A1-WEEKDAY(A1)+7
NorieAnalyst Assistant
Commented:
Perhaps.

=TEXT(TODAY()-WEEKDAY(TODAY()), "dd-mm-yyyy")&" to "&TEXT(TODAY()-WEEKDAY(TODAY())+6, "dd-mm-yyyy")
These formulas will give you the requested results and accounts for if today is Saturday. For the start date you can use:
=IF(WEEKDAY(A1)=6,A1-6,A1-WEEKDAY(A1))

Open in new window

and the end date use:
=IF(WEEKDAY(A1)=6,A1,A1-WEEKDAY(A1)+6)

Open in new window

Just change "A1" to the cell containing your date.

Paul

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial