# Excel question -- date range

**URL:** <https://boards.straightdope.com/t/excel-question-date-range/616526>\
**Category:** Factual Questions\
**Created:** [March 22, 2012, 6:19pm UTC](https://boards.straightdope.com/t/excel-question-date-range/616526 "2012-03-22T18:19:01Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Greg\_Charles](https://avatars.discourse-cdn.com/v4/letter/g/839c29/32.png) [@Greg\_Charles](https://boards.straightdope.com/u/Greg_Charles)\
**Post date:** [March 22, 2012, 6:19pm UTC](https://boards.straightdope.com/t/excel-question-date-range/616526/1 "2012-03-22T18:19:01Z")

</div>

I have an Excel template for entering my weekly time at work. I just put in Saturday’s date on the first column, and the rest of the headers fill in automatically. (Saturday is the start of the billing week. However, they’ve just demanded there be an overall header near the top saying:

**Time Sheet for 3/17/2012 – 3/23/2012**

I’d like that to be built automatically from the one date I enter, but I can’t figure out how to do it.

---

<div class="post-metadata">

**Author:** ![just\_some\_guy5](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@just\_some\_guy5](https://boards.straightdope.com/u/just_some_guy5)\
**Post date:** [March 22, 2012, 6:53pm UTC](https://boards.straightdope.com/t/excel-question-date-range/616526/2 "2012-03-22T18:53:33Z")

</div>

Try this:

=“Time Sheet for”&text(cell that contains 3/17, “mm/dd/yyyy”)&" – "&text(cell that contains 3/23, “mm/dd/yyyy”)

For example, if cell A4 contained 3/17 and cell F4 contained 3/23, you’d type the following for your header:

=“Time Sheet for”&text(a4, “mm/dd/yyyy”)&" – "&text(f4, “mm/dd/yyyy”)

---

<div class="post-metadata">

**Author:** ![just\_some\_guy5](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@just\_some\_guy5](https://boards.straightdope.com/u/just_some_guy5)\
**Post date:** [March 22, 2012, 6:55pm UTC](https://boards.straightdope.com/t/excel-question-date-range/616526/3 "2012-03-22T18:55:57Z")

</div>

As an FYI, you could alternatively use m/d/yyyy in place of mm/dd/yyyy (this would show up as 3/17/2012 instead of 03/17/2012).

---

<div class="post-metadata">

**Author:** ![Duke](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@Duke](https://boards.straightdope.com/u/Duke)\
**Post date:** [March 22, 2012, 7:07pm UTC](https://boards.straightdope.com/t/excel-question-date-range/616526/4 "2012-03-22T19:07:18Z")

</div>

And for short date (3/17/12) you’d substitute m/d/yy.

If you know the interval is always going to be a week, and so thus the end date is always six days after the start date, you could simplify it even further by only using the start date:

=“Time Sheet for”&text(a4, “m/d/yyyy”)&" – "&text((a4+6), “m/d/yyyy”)

That way, you don’t have to include an extra cell with the end date, just the Saturday which the billing week begins with.

---

<div class="post-metadata">

**Author:** ![Greg\_Charles](https://avatars.discourse-cdn.com/v4/letter/g/839c29/32.png) [@Greg\_Charles](https://boards.straightdope.com/u/Greg_Charles)\
**Post date:** [March 23, 2012, 12:56am UTC](https://boards.straightdope.com/t/excel-question-date-range/616526/5 "2012-03-23T00:56:18Z")

</div>

Yes, that did it. Thanks much!