Addin to Create A Date Range

Summarizing data with dates can be difficult. For example, if you have a list of invoices with various dates, how do you summarize that for reporting? If you want to report on weekly figures, you could use a helper column and drop in a =WEEKNUM(cell) formula to get an integer of the week. To report on monthly figures, you could use =MONTH(cell) to get the integer of the number of the month (1 for January).

But when looking at reports, nobody wants to see week numbers of the number of the month—they want to see something relatable. We're going to look at three solutions to create fields to report on: 1. Week Beginning: for each invoice date, this gives us the Sunday date of that week. 2. Week Ending: for each invoice date, this gives us the Saturday date of that week. 3. Week Period: this gives us the Sun-Sat dates (03/31/19 - 04/06/19)

We'll then turn the Week Period into an add-in so that we can reuse it with ease.

Here's the data we'll be working with. Copy and paste it into your spreadsheet:

Invoice Date	Qty	Description	Unit Price	Amount
4/1/2019	1	Apples	0.79	$0.79
4/2/2019	2	Oranges	0.98	$1.96
4/3/2019	3	Apples	0.79	$2.37
4/4/2019	4	Bananas	1.12	$4.48
4/5/2019	2	Strawberry	2.34	$4.68
4/6/2019	4	Apples	0.79	$3.16
4/7/2019	5	Bananas	1.12	$5.60
4/8/2019	3	Strawberry	2.34	$7.02
4/9/2019	2	Apples	0.79	$1.58
4/10/2019	2	Bananas	1.12	$2.24
4/11/2019	5	Oranges	0.98	$4.90
4/12/2019	1	Apples	0.79	$0.79
4/13/2019	2	Oranges	0.98	$1.96
4/14/2019	3	Apples	0.79	$2.37
4/15/2019	4	Bananas	1.12	$4.48
4/16/2019	2	Strawberry	2.34	$4.68
4/17/2019	4	Apples	0.79	$3.16
4/18/2019	5	Bananas	1.12	$5.60
4/19/2019	3	Strawberry	2.34	$7.02
4/20/2019	2	Apples	0.79	$1.58
4/21/2019	2	Bananas	1.12	$2.24
4/22/2019	5	Oranges	0.98	$4.90

The Formula's

Let's drop in three helper columns to the right of our data:

In cell F2, enter the formula =A2-WEEKDAY(A2)+1

In cell G2, enter the formula =A2-WEEKDAY(A2)+7

Which gives us (make sure these cells are formatted as dates):

And now in cell H2 we will combine these formulas to get the Sun-Sat dates. They have to be formatted as text, otherwise the underlying integer date values will just be added together and we would end up with some bizarre future date.

=TEXT(A2-WEEKDAY(A2)+1, "mm/dd/yyyy")&" - "&TEXT(A2-WEEKDAY(A2)+7, "mm/dd/yyyy")

Drag the formula's down and you end up with three helper columns to help you report on the data.

Creating the Add-in

The formula's above can be difficult to remember. With a user-defined function (UDF) we can re-use these formula's in any workbook we want just like we would with any built-in Excel formulas.

1. Open a separate workbook and right click on the worksheet tab: select View Code

2. In the menu, click Insert > Module.

   

3. Copy and paste the following into the white space:

   Function WEEKBEGINNING(rng As Range) WEEKBEGINNING = (rng - Weekday(rng) + 1) End Function Function WEEKENDING(rng As Range) WEEKENDING = (rng - Weekday(rng) + 7) End Function Function BILLINGPERIOD(rng As Range) BILLINGPERIOD = Format((rng - Weekday(rng) + 1), "mm/dd/yy") & " - " & Format((rng - Weekday(rng) + 7), "mm/dd/yy") End Function