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.
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”)
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).
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.
Yes, that did it. Thanks much!