## Create a monthly date range

I will demonstrate three different techniques to build monthly date ranges in this article. Two of these techniques are easy because they have the start and end date in a cell each.

To have two dates in the same cell makes it more complicated but I have a solution for that, as well.

### Create date ranges using a built-in feature

To build a date range that begins with the first date in a month and ends with the last date in a month follow these steps:

- Type the start date of your first date range in one cell
- Type the end date of your first date range in the next cell
- Repeat above steps to enter the second date range in cells below

- Select all cells
- Click and hold on black dot

- Drag to cells below as far as needed

Excel dates are actually numbers, the above technique uses Excel's ability to quickly create number sequences using the selected cells as a guide to determine the size of the date ranges below.

### Basic formula

Type the start date in a cell, in this case, 1/1/2017. Type the following formula in the next cell:

The formula in cell C3 calculates the last date for the month and year in cell B3. It actually calculates the first date in the next month and then subtracts with 1.

This solves the issue with some months having 30 days and some having 31 and February with 28 days in common years and 29 days in leap years.

The next date range has the first date in the next month as the start date. The formula is almost identical to the previous formula.

Formula in cell B4:

Now copy cell C3 and paste to cell C4.

The relative cell references in the formula changes accordingly, you can read more about relative and absolute cell references here:

How to use absolute and relative references

What is a reference in Excel? Excel has a A1 reference style. Columns are named letters A to XFD. Total […]

Copy cell range B4:C4 and paste to cells below as far as needed.

### Advanced formula

The following picture shows you date ranges in a single cell each. To get that you need to build a somewhat more complicated formula.

### Formula in A4:

copied right as far as necessary. The TEXT function formats the number from the DATE function as a readable text string in a format you choose. You can read more about the TEXT function here:

Excel function syntax TEXT(value, format_text) Converts a value to text in a specific number format. Arguments value - The string […]

### Formula in A7:

copied right as far as necessary.

### Formula in A10:

copied down as far as necessary.

### Formula in A19:

copied down as far as necessary.

### Download *.xlsx file

Create a monthly date range.xlsx

### Functions in this article

**ROW(**reference**)** returns the row number of a reference

**DATE(**year, month, day**)** returns the number that represents the date in Microsoft Office Excel date-time code

**TEXT(**value, format_text**)**

Converts a value to text in a specific number format

Question: how can i get the date range as : 01/01/09 - 01/15/09, 01/16/09 - 01/31/09 and so on I […]

Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

Count a specific weekday in a date range

NETWORKDAYS function returns the number of whole workdays between two dates. The array formula in this blog post counts for […]

I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]

Excel function syntax TEXT(value, format_text) Converts a value to text in a specific number format. Arguments value - The string […]

How to remove numbers from a cell value

The array formula in cell C3:C7 extracts everything except numbers from cell B3. The following formula contains the TEXTJOIN function […]

How to extract numbers from a cell value

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]

How to convert radians to fractions of pi

The formula in cell D3 converts the radian decimal value to fractions of pi. Formula in cell D3: =TEXT(C3/PI(),"?/?")&"*"&CHAR(182) Explaining […]

### 2 Responses to “Create a monthly date range”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

need to truncate the undermentioned column to create columns of weeks it has, for eg. 1/26/2011 was a wed, so need a column for 1/26/2011-sun 1/29/2011, and then onwards each week starting monday till 8/31/2011. please help asap

Assigned Dates

1/26/2011 - 8/31/2011

2/1/2011 - 3/30/2011

2/1/2011 - 3/30/2011

2/1/2011 - 3/30/2011

11/1/2010 - 2/11/2011

1/26/2011 - 8/31/2011

Deeks,

read this post: Filter weeks from a date range