Excel 2010 calculate week beginning date and week ending date

How can I calculate the beginning date of the week and the ending date in a week to show like this 6/1 - 6/7.  I inherited a report that needs to keep this format going.  The week starts on a Monday 00:00:00 and ends on Sunday 23:59:59.  All I need is to show the month/day for the beginning of the week and the end of the week and all of this needs to be in one cell.
Rrave26Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

=TEXT(TODAY()-WEEKDAY(TODAY())+2."M/D")&" - "&TEXT(TODAY()-WEEKDAY(TODAY())+9,"M/D")

EDITED Formula

Regards
0
Roy CoxGroup Finance ManagerCommented:
There's a typo in the formula, it should be

=TEXT(TODAY()-WEEKDAY(TODAY())+1,"M/D")&" - "&TEXT(TODAY()-WEEKDAY(TODAY())+8,"M/D")
0
Rob HensonFinance AnalystCommented:
You can use the FLOOR and CEILING functions for this:

=FLOOR(TODAY(),7)

Gives the previous Saturday so adding +2 on the end will give the Monday date

=CEILING(TODAY(),7)

Gives the following Saturday so adding +1 will give the Sunday.

So combination of the two with a dash:

=TEXT(FLOOR(TODAY(),7)+2,"mm/dd")&"-"&TEXT(CEILING(TODAY(),7)+1,"mm/dd")

Thanks
Rob H
0
Rgonzo1971Commented:
Corrected typo and formula

=TEXT(TODAY()-WEEKDAY(TODAY())+2,"M/D")&" - "&TEXT(TODAY()-WEEKDAY(TODAY())+9,"M/D")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
For completeness, the FLOOR and CEILING functions work the same as MROUND, ie they round a number to the specified factor. The difference being that MROUND will round to the nearest whereas FLOOR will always go down and CEILING will always go up.

Due to the way that Excel stores dates, day 1 of the Excel calendar** is 01/01/1900 which was a Sunday and therefore every 7th day was a Saturday, therefore the serial number for any Saturday will always be a factor of 7.

**Unless using 1904 system.

Thanks
Rob H
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.