## Create a custom date range

*Article updated on February 16, 2018*

**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 have discussed before how to build date ranges using formulas and a built-in feature using number sequences. Please read that if you don't need a date range in the same cell.

The following picture shows you a formula that returns date ranges in a single cell using 14 days interval.

### Array formula in A4:

copied rigt as far as needed.

### Array formula in A7:

copied rigt as far as needed.

### Array formula in A10:

copied down as far as needed.

### Array formula in A18:

copied down as far as needed.

### Functions in this article:

**ROW(**reference**)** returns the rownumber 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

**IF(**logical_test,[value_if_true],[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Extract unique distinct values sorted based on sum of adjacent values

Table of Contents Filtering unique distinct text values and sort them based on sum of adjacent values Filtering unique distinct […]### 8 Responses to “Create a custom date range”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

I have the list of dates:

1/4/2010

1/5/2010

1/6/2010

1/7/2010

1/8/2010

1/11/2010

1/12/2010

1/13/2010

1/14/2010

1/15/2010

1/19/2010

1/20/2010

1/21/2010

Is there a formula that I could use to give me a range that looks like: 1/4/2010-1/21/2010 ?

@A.R.

Assuming you know the starting cell for you list (assumed to be A2 in the formula below) and that the list changes so you do not know what cell the last date will be in (and assuming there is no data after the last date cell)...

=TEXT(A2,"m/d/yyyy-")&TEXT(LOOKUP(2,1/(A1:A65535""),A:A),"m/d/yyyy")

Change the A65535 reference to A1048575 if you are using XL2007 or above.

I have a file with admit and discharge dates for patients. So I have the begin and end dates of their time in the hospital and I want to create a spreadsheet that will list the dates between the admit and discharge dates. So, for instance, I will have an admit and discharge date range of 1/1/2012-1/4/2012. I would like the function to list 1/1/2012, 1/2/2012, 1/3/2012, 1,4,2012. Is this possible?

Susan,

I am not sure I understand, do you want the dates concatenated in one cell?

I made this file, it lists dates in column d:

Susan.xlsx

I have something needed that is similar to Susan. I need it not to show the dates in between yet I need to be able to hit control F and find them even if its between the two dates.

Ex: I have a box with documents ranging from 1971 to 1980. I want to put in a cell that says the date of documents so I would put 1971-1980, but the problem with that is I need it to count the years in between.

So that way if I hit "control F" and type in 1975 it will find it and take me to that cell describing that box.

Please advice, does that help?

I have a file which runs over a 52 week period for tracking man hours.

I need a formula that moves forward each week looking back over the last 12 week period so I can calculate the amount of hours used by each person. So for example if a person works 12 hours in that 12 week period I would like to know but once the hours go out of that 12 week period the information is no longer required.

Mark

Hi There

I have to work out a weekly schedule on a 6 week rotation. for around 2 groups. Is an easier way than the below formula:

First Week:

="Period from "&TEXT(DATE($B$1;$B$2;$B$3);"dd mmmm yyyy")&" to "&TEXT(DATE($B$1;$B$2;$B$3+7);"dd mmmm yyyy")

Last week:

="Period from "&TEXT(DATE($B$1;$B$2;$B$3+35);"dd mmmm yyyy")&" to "&TEXT(DATE($B$1;$B$2;$B$3+42);"dd mmmm yyyy")

To edit the formula by adding 7 to the existing range allows gaps for errors that would preferably need to be avoided

Hi, I need to calculate how many days our railcars were idle during the month. I have the begin and end dates and I want to create a spreadsheet that will automatically calculate the number of days within those dates.

Ex: Railcar arrived on 10/1/16 (Begin date) and left on 10/16/2016 (End date). I want the 3rd column to automatically calculate the 15 days it was idle on site.

Thank you.