Excel 2013 Date Range Calculation

Rrave26
Rrave26 used Ask the Experts™
on
I have an excel report that reports data by week and the date range is in one column (B), i.e., 12/7 - 12/13, 12/14 - 12/20, 12/21 - 12/27, 12/28 - 1/3, 1/4 - 1//10, 1/11 - 1/17, 1/18 - 1/24 and 1/25 -  1/31... I do this for all 52 weeks of the year and then start over again with the new year.  This reports data from Mon Morning starting at 00:00 and reports through Sunday night at 23:59.  

I need help with creating a formula that calculates the weeks range for all 52 weeks.  Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

If it's the text for the column ( use a helper' column in Col A with the first date then this value +7
and in colB
=TEXT(A1,"MM/DD - ")&TEXT(A1+6,"MM/DD")

Open in new window

and for the time pls try
=A1

Open in new window

in Col C and
in Col D
=C1+7-TIME(0,0,1)

Open in new window


you can then fill down the cells

Regards

Author

Commented:
Thanks.  It works perfectly.  I just wasn't thinking about a helper column.

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