Create a date range [Formula]
Cell A1 1/4/20091/10/2009
Cell B1 1/11/20091/17/2009
Cell C1 1/18/20091/24/2009
How do I create a formula to do this?
I will in this article discuss what Excel dates actually are, how to use them in formulas and how to create a sequence of dates that you can use as date ranges.
What is on this page?
 Explaining Excel dates
 Basic date ranges
 Create a date sequence
 Advanced formula
 Explaining advanced formula
 Download workbook
What are dates in Excel?
First, what are dates in Excel? They are actually numbers and I will prove it to you, try these steps:
 Type a date in a cell
 Select the cell
 Press CTRL + 1 to open the "Format Cells" dialog box

Select "General"
 Click OK button
 The cell you selected now has a different formatting. This shows you what dates are in Excel.
Date 11/15/2017 is 43054. Excel starts numbering dates at 1/1/1900 with value 1. Type 1 in a cell and change the cell formatting to "Date" and see what Excel displays.
Date 11/15/2017 is 43053 days from 1/1/1900. This means also that you can't use dates prior to 1/1/1900.
Recommended articles
 How to format date values
 Calculate last date in month
 Find latest date based on a condition
 Workdays within date range
Basic date ranges
You can build a formula or use a builtin feature to build date ranges, read on to learn more.
Now you know that dates in Excel are numbers. You can easily create a date range by adding a number to a date.
The picture below shows a start date 11/15/2017, adding number 7 to that date returns 11/22/2017
This allows you to quickly build date ranges simply by adding a number to a date.
Now select cell B4 and type =C3+1
Copy cell C3 and paste to cell C4.
Relative cell references changes when you copy a cell and paste it to a new cell and I am going to use that now.
Copy cell range B4:C4 and paste it to cells below.
You have now built multiple date ranges using simple mathematics.
Create a date sequence
Excel has a great builtin feature that allows you to create number sequences in no time. Since dates are numbers in Excel you can use the same technique to build date ranges.
To build date ranges that have the same range but dates change, follow these steps:
 Type the start date and the end date in a cell each

Type the second start date an end date in cells below
 Select both date ranges

Click and hold on black dot

Drag to cells below

Release mouse button
Excel has now built date ranges automatically using the two first date ranges as a template.
Recommended articles
 Use MEDIAN function to calculate overlapping ranges
 Identify overlapping date ranges
 Count overlapping days across multiple date ranges
 Find missing dates in a set of date ranges
 Category: Overlapping date ranges
Advanced formula
If you need to build date ranges that have both the start and end date in the same cell you need to build a more complicated formula. The TEXT function lets you format dates out of numbers.
See row 3 in the above picture.
Date ranges horizontally
The following formula returns date ranges that exists after the start date, the start date in this case is 1/4/2009, however you can easily change that.
The DATE function creates the number using three arguments. The first argument is the year, the second argument is the month and the third argument is the day.
DATE(year, month, day)
Formula in cell A3:
Copy cell A3 and paste to cells to the right as far as needed.
This formula returns date ranges that are prior to the given start date.
Formula in A6:
Date ranges vertically
Formula in A9:
Formula in A18:
How the formula in cell A3 works
The goal with the formula above is to create a date range from Sunday to Saturday. When cell A3 is copied and pasted to the right, the date range adjusts accordingly.
The formula consists of two parts. The first part calculates the start date of the date range. I have bolded the start date part of the formula below.
=TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)1)*7, "mm/dd/yy")&""&TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)1)*7+6, "mm/dd/yy")
Step 1  First part of the formula creates the start date (bolded)
I have bolded the start date in this date range: 01/04/09  01/10/09.
TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)1)*7, "mm/dd/yy")
DATE(2009, 1, 4) is the start date of the date range series.
DATE(2009, 1, 4) returns 39817
(COLUMN(A:A)1)*7 creates a number determined by the current column.
COLUMNS($A:A) returns 1.
COLUMNS($A:A)  1 returns 0.
(COLUMNS($A:A)1)*7 returns 0.
This number creates how many days there is between start dates in the date ranges.
Start dates are bolded below.
Example
01/04/09  01/10/09 ; 01/11/09  01/17/09 ; 01/18/09  01/10/09
11  4 = 7, 18  11 = 7.
When the formula is copied and pasted to the right, the relative reference in COLUMNS($A:A) changes.
This cell reference changes as the formula is copied and pasted to the right.
Example,
COLUMNS($A:A) is 1
COLUMNS($A:B) is 2
COLUMNS($A:C) is 3
TEXT(DATE(2009, 1, 4)+0, "mm/dd/yy")
becomes
TEXT(39817 + 0, "mm/dd/yy").
"M/DD/YY" is the specified number format.
TEXT(39817 + 0, "mm/dd/yy") returns 01/04/09
Step 2  Second part of the formula creates the end date
The second part of the formula is
TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)1)*7+6, "mm/dd/yy")
The only difference between the first part of the formula:
TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)1)*7, "mm/dd/yy")
and the second part of the formula:
TEXT(DATE(2009, 1, 4)+(COLUMNS($A:A)1)*7+6, "mm/dd/yy")
is +6 (bolded)
Example
01/04/09  01/10/09
10  4 = 6
Recommended reading
Plot date ranges in a calendar
The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
Count dates inside a date range
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
Use MEDIAN function to calculate overlapping ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]
how can i get the date range as : 01/01/09  01/15/09, 01/16/09  01/31/09 and so on
=TEXT(DATE(2009, 1, 4)+(ROW(1:1)1)*14, "mm/dd/yy")&""&TEXT(DATE(2009, 1, 4)+(ROW(1:1)1)*14+13, "mm/dd/yy") + ENTER copied down as far as necessary.
Put your START date in BOTH spots that say (2009, 1, 4)...
Try this: =TEXT(IF(MOD(COLUMNS($A$1:A1), 2), DATE(2009, ROUND(COLUMNS($A$1:A1)/2, 0), 1), DATE(2009, ROUND(COLUMNS($A$1:A1)/2, 0), 16)), "MM/DD/YY")&""&TEXT(IF(MOD(COLUMNS($A$1:A1), 2), DATE(2009, ROUND(COLUMNS($A$1:A1)/2, 0), 15), DATE(2009, ROUND(COLUMNS($A$1:A1)/2, 0)+1, 1)1), "MM/DD/YY") copied right as far as necessary.
I guess the formula can be done a lot smaller.
See this blog post: https://www.getdigitalhelp.com/2009/11/04/createacustomdaterangeinexcel/
this isn't working when i copy and paste?
clay,
There seems to have been a typo.
You may also need to adjust mm/dd/yy depending on your regional settings.
https://www.techonthenet.com/excel/formulas/text.php
Thanks for commenting!
Hey there! Awesome help!
I need something similar to this, however, with an added piece.
I need a formula that if I enter a start date in field B1 such as 6/8/11 it will give me the date ranges for 7 days in fields B3B14. Does that make sense?
Basically I want a formula that will tell me when a client is admitted to services on 6/8/11, their week 1 is 6/8/11 to 6/15/11; week 2 is 6/16/116/22/11 etc through 12 weeks.
I want the initial date in B1 to be the only value that I have to change to produce these results. Is that possible?
Shannon,
read this post: Date ranges for 7 days in excel
Thank you for commenting!
how do i get a range of date to show up.. in the first cell I want 1/11/11 and then each cell to follow the next date
example
b3 has 1/11/11 and then i want all 31 days to follow with out having to put each on in b4, b5, b6 and so on?
todd,
Right click and hold on black dot.
Drag down and release right mouse button. Click Fill series.. with left mouse button.
Hi I need help with a formula. Basically I have a date column and a true of false value column. What I need is a formula that says if the date in Column J is between 4/1/113/31/12 then enter 1 in Column M, if not enter 0 in Column M.
I hope you can help.
Thanks
Michelle,
Formula in cell M1:
perfect thank you Oscar
Michelle,
You are welcome!
Thank you!I used my extensive QA background to spot the use of a : and incorrect spacing in the formula for a6....Once again THANK YOU!
Here's what I ended up with
=TEXT(DATE(2011, 9, 19)+(COLUMNS($A:A)1)*7, "mm/dd/yy")&""&TEXT(DATE(2011, 9, 19)+(COLUMNS($A:A)1)*7+6, "mm/dd/yy")
DanL,
Thanks! I have updated this post.
I am trying to do a formula in excel 2003. It is quite complex.
The start date was June 13 and I need to reflect any blank cells that are 60 days out and any cells that are 91 days out.
The expirey dates are only for certain cells ($B$3:$F$3 and $N$3:$S$3).
Hope that makes sense :(
Richelle,
can you describe your question in greater detail?
Also add some example data and what you are trying to achieve?
Sure,
Employee Name Company Start date  WHMIS  Orientation  H/R
John Smith  17 Oct 11  BLANK  17 Oct 11 BLANK
Johanne Smi  16 Oct 11  BLANK  BLANK BLANK
I have an expirey date of 60 days (will be yellow in color after 60 days from the start date) and 91 days (will be red in color after 91 days from the start date). I only need the expirey dates for the Whimis and HR cells the orientation cell does not require an expirey date.
I have tried this:
=today()$b$12>60 and =today()$b$12>91 and =today()$b$12>59
that formala does not seem to work...
Richelle,
You are using absolute cell references in your conditional formatting formula.
You are also only using cell $b$12 when you say "I only need the expirey dates for the Whimis and HR cells". $b$12 is the company Start date?
you bet, $b$12 is the company start date. I am wanting the whole line to turn red/yellow when Whimis and HR are over due. The start dates are behind so I dont think (=TODAY)would work.
Richelle,
I am wanting the whole line to turn red/yellow when Whimis and HR are over due
In your example table, Whimis and HR are blank?
Employee Name Company Start date  WHMIS  Orientation  H/R
John Smith  17 Oct 11  BLANK  17 Oct 11 BLANK
Johanne Smi  16 Oct 11  BLANK  BLANK BLANK
WHIMIS and H/R are Blank.
Richelle,
Download file:Richelle.xls
Not quite, when the dates are added it does not return to normal.
Dear Oscar
example: from 20/3/2000 to 31/10/2011 = 11.58, what is the formula i should use to get the result like this?
Richelle,
Download file: Richelle1.xls
You ROCK!
Thank you so much!
Shirley,
What are you trying to do?
Create dates with 11.58 years between them?
or
Calculate years between two dates?
Calculate years between two dates. :)
Shirley,
Is 0.58 a decimal fraction of a year?
I get 0.62 in my calculations.
Oscar,
I think yours is the correct one as i got 0.58 from my system which I found not accurate.
Shirley,
Formula in cell c4:
=TEXT(A4, "DD/MM/yyyy")&" to "&TEXT(B4, "DD/MM/yyyy")&" = "&ROUND((B4A4)/365, 2)
This formula does not take leap years into account.
Excel dates: https://www.cpearson.com/excel/datetime.htm
thank you Oscar! i got the solution.
Dear Oscar,
I am really need your help in excel.
I want to sort some data in date between nov/1/2011 to nov/16/2011.this date is in column K.
what should I type the formula?
Thank you.
I have a question as well.
I have a column consisting of date, user, and another variable (lets call it food) on a sheet:
so ex.
A  B  C

Date  user2  carrots
and it currently counts the number of times a user has eaten a food using the countifs function:
=COUNTIFS(Sheet2!B2:B9000,"=user1",Sheet2!C2:C9000,"=peas")
...this works perfectly.
The problem Im having is that I want to create another sheet where you can enter a date range and have it display only the results in that range, including the dates.
Any suggestions/help would be appreciated.
Came up with a solution  make a true/false date range column and pulled the data from that. Thanks anyway, learned a lot!
How can I create a data range for different data apart from dates say personnel ID #s? the formula given here is for dates..
Ian,
Can you give me some examples of what you want to achieve?
Hi Oscar,
First of all, helpful blog!
May I ask here regarding finding the correct value based on and within the date range?
For example, column A contains all the different dates like (not arranged or sorted):
(1) 1/10/2011
(2) 23/3/2012
(3) 20/12/2011
(4) 3/1/2012
Reference list:
In column C, contains the expiration dates with corresponding values in column D.
For example (chronologically arranged):
31/05/2010 100,000.00
31/08/2010 200,000.99
30/11/2010 300,000.88
28/02/2011 400,000.77
31/05/2011 500,000.66
31/08/2011 600,000.55
30/11/2011 700,000.44
29/02/2012 800,000.33
25/03/2012 900,000.22
The Result (based on and before the date in Column C but the value in Column D):
(1) 1/10/2011 400,000.77
(2) 23/3/2012 800,000.33
(3) 20/12/2011 700,000.44
(4) 3/1/2012 800,000.33
Thanks for your time,
 oui
Hi Oscar,
Kindly disregard my previous message.
I think I've got it.
I've used LOOKUP function.
Thanks though,
 oui
I have a weekly report for work I use. My template needs to have the date formatted across one of the upper rows. I need it to read. January 26, 2012.
Thanks,
Matt
Here is a solution to oui´s question:
Learn more: Return value if in range in excel
Matt,
That is not an easy question! I think you have to change formatting for each date.
Example,
Value in cell A1: 40910
1. Select cell A1
2. Press Ctrl + 1
3. Go to tab "Number"
4. Select custom in category
5. Type MMMM D"6, " yyyy
6. Click OK
Hi, Oscar! I went through all the Q and As, but not sure my question is answered. It's actually quite simple.
I need a date range, pulling the date from one column.
Column I:
DATE
17Jun11
Column J:
Date range (7 days after date in Column I to 21 days after date in Column I).
Further, is there any way to put into column K whether or not the date is out of the range, with a Y/N, or other means to denote out of range date?
THANKS for your help, IN advance!Ingrid
Ingrid,
Date in cell I2: 17Jun11
Formula in cell J2:
This is awesome! THANK YOU!ij
Hi Oscar, I'm looking for help in Excel.
In one column, I've dates for few years. I would like to have a column next to it which shows the corresponding Weeks, like Week1, Week2,...till Week5. Can you please help me with this?
Chaks,
I hope you´ll find an answer here:
https://www.rondebruin.nl/weeknumber.htm
or here:
https://www.cpearson.com/excel/WeekNumbers.aspx
When does week1 begin? There are few possible ways to number weeks. See links.
Hi Oscar, I need some help for excel.
I have 1 column containing the 365 days of 2010 written in short date like 01/01/2010. Then I have a second column with some cells containing data and some others left blank.
I want a formula that will write 1 if at least 50% of cells for a given month are fill and leave a blank cell if not. I want this formula to be aware of the month shift and easy to drag so that I will only get 12 values for a year.
I think this is impossible but you are the pro.
Ricardo,
It is possible!
Download excel file:
Ricardo.xls
Hello Oscar,
I am working with your date range formula from this post (Create a date range using excel formula). I am using the formula from A9, which includes ROW in the formula. Because all of the other cells in those rows are empty I get error warnings. Is there a way to avoid the error warnings regarding empty cells?
Many thanks.
Chaks
use this:
=INT((DAY(cell reference containing your date)1)/7)+1
Chaks,
Correction!
Use this if you want to consider sunday as the first day of the week:
=INT((13WEEKDAY(cell reference containing your date)+DAY(cell reference containing your date))/7)
the first formula would only consider the value of the days, and is not what you were looking for I think...
Flyingwater,
I am working with your date range formula from this post (Create a date range using excel formula). I am using the formula from A9, which includes ROW in the formula. Because all of the other cells in those rows are empty I get error warnings. Is there a way to avoid the error warnings regarding empty cells?
Formula in cell A9:
I don´t think ROW function is the problem here, maybe the "mm/dd/yy" gives you trouble?
I am trying a formula which gives me the month between dates what fall between 2 months. For example, 21/02/2012  20/03/2012 should return March
Adiel,
What if the date range is 21/02/2012  20/04/2012?
I am trying to get the date range that was used in a previous query to show up on the front page of my report. The Date range is selected by the user after running the Macro. I want that date range to be listed so when you run the Report you can visually see what date range was pulled. Lets say the Query name was Query1 and the field name was Field2. Oscar, Any idea?
Thanks!
Hi Oscar
I am specifically look at 21st of the month to 20th of the next month only. is it is 21/02/2012  20/04/2012 it should return an error.
Daniel,
Hard to say without seeing your workbook.
Adiel,
Is 21/02/2012 in one cell and 20/03/2012 in a different cell?
I have a spreadsheet that needs to report the number of days a certain document is over 3 shift days. Where I work is a 9 on and 5 off schedule and I need to exclude any of the days off so that the over 3 is actually only over 3 work days. I am thinking the NETWROKDAYS function may be used for this but can you help with this??
Glenn,
Can you provide some example data and what you want to achieve?
Добрый день, Оскар. Помогите решить проблему. Уже который день мучаюсь и все никак не получается посчитать.
Файл с данными я залил на обменник (mail.ru), вот ссылка, чтобы Вы сразу все увидели и поняли мои замыслы
https://files.mail.ru/CJAX0S
Мне необходимо посчитать сумму получившихся цифр за каждый месяц. Хочу задать формулу, которая находит из столбца "А" все даты относящиеся к нужному месяцу и посчитать сумму цифр из столбца "К". Строки в месяце могут добавляться и убираться, следовательно количество цифр изменяется и каждый раз пересчитывать  жутко неудобно.
Для примера покажите на одной цифре  сумма данных за февраль 2012.
Результат находится в L23.
Снизу ТАБ и справа вверху есть разные варианты формул, которые я пытался составить, но так ни одна и не заработала.
Заранее СПАСИБО! )))
Re Сергей, it could be translated as:
Hello, Oscar. Help me solve this problem. I am tormented day after day, and still can not get to "count".
Data file, I filled in the exchanger (mail.ru), here is a link for you to understand all of my designs
"link to his file"
I need to calculate the amount of the resulting figures for each month. I want to set a formula that finds the column "A" all dates relating to the desired month and calculate the sum of numbers in column "K". Rows can be added in the month to get out and therefore the number of digits is changed each time to count  terribly "uncomfortable".
For example, show one figure  the amount of data for February 2012.
The result is in L23.
BottomTAB and right at the top there are different versions of the formulas I have tried to make, but no one and did not work.
Thanks in advance! )))
Hi Oscar,
I'm hoping you can help me with my formula question. I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes take different times to complete (ie. one step could only take a week, another could take up to 4 weeks). Is there a formula I can use to calculate each step in the process based off of the date range of completion for the first step in the process?
Let me know if you need more information.
Danielle,
Your question seems interesting but I am not sure I fully understand. Can you provide an example?
Sure! (Sorry i couldn't figure out how to paste this as an excel sheet)
So for example, if you look at the BBD date at the bottom, all of the steps above it take a certain amount of time to complete and have to be finished on time in order for the project to be complete by 1/4/2013. Instead of typing in manually the date ranges I am trying to write a formula that will allow me to input the project date (ie 1/4/2013) and have all of the other steps populate themselves based on how long they take to complete (ie. the manuscript to CE step could take 2 weeks, the manuscript from CE could take 1 week and so on). I hope that makes sense??
ie. ONE ROUND From: To:
Manuscript turnover 6/25/2012 7/30/2012
Manuscript to CE 8/6/2012
Manuscript from CE 8/20/2012
Manuscript to author 8/27/2012
Manuscript from author 9/10/2012
Ms to comp 9/3/2012 9/17/2012
Pages from comp 10/8/2012
Pages from author 10/22/2012
Pages to proofreader 10/29/2012
Pages from proofreader 11/12/2012
Pages to comp 11/19/2012
Confirming proofs 11/26/2012
Ship to printer 12/3/2012
BBD 1/4/2013
Danielle,
read this post:
Calculate dates in each step in a project based on a finish date
Thanks! I posted my comment under the other link!
Hi,
The above formula in A9 "Increasing date in a column" works pretty good however I have a small request to change the formula as per my need.
As I fill the first column in excel with this formula than use + handler on bottom right of the column to drag and copy it to next cell so the date increases. That works but when it reaches the end of month it continues in the same cell. Is it possible so at the end of the month the range would stay within the month instead of increasing to next month?
Here let me try to explain visually.
01/01/1201/07/12
01/08/1201/14/12
01/15/1201/21/12
01/22/1201/28/12
01/29/1202/04/12 < can this be 01/29/1201/31/12 ?
And next month would be per week in each coulmn as well and so on.
02/01/1202/04/12
02/05/1202/11/12
02/12/1202/18/12
02/19/1202/25/12
02/26/1202/29/12
03/01/1203/03/12
Thank you.
Anees,
Your question is really complicated and I like that. But unfortunately I don´t have an answer right now.
I have the exact same question as Anees and I was just wondering if you have found a solution. Thanks
Anees and charles,
Read this post:
Date ranges: Weeks within a month
hey oscar ... i used the date range formula in your A3 example above. it's great! thanks! now i'm trying to reference these dates ranges in a sumifs statement. for example (if i can do this) ... my current formula is:
=SUMIFS('PO LOG'!$L$5:$L$1036,'PO LOG'!$K$5:$K$1036,SUMMARY!A10,'PO LOG'!$J$5:$J$1036,SUMMARY!D$3)
'PO LOG'!$L$5:$L$1036: column L lists invoice dollars amounts
'PO LOG'!$K$5:$K$1036: column K lists expense codes
SUMMARY!A10: individual expense code
'PO LOG'!$J$5:$J$1036: column J list invoice dates
SUMMARY!D$3: date range created from your A3 formula above (ex: 02/19/1202/25/12)
this formula is returning zero dollar amounts  which obviously isn't correct. however, i'm not getting an error with the formula. any help you can give would be amazing! thanks so much.
best ... josh
Josh,
Try this formula:
=SUMIFS('PO LOG'!$L$5:$L$1036,'PO LOG'!$K$5:$K$1036,SUMMARY!A10,'PO LOG'!$J$5:$J$1036,"<="&RIGHT(SUMMARY!D$3, LEN(SUMMARY!D$3)SEARCH("", SUMMARY!D$3)),'PO LOG'!$J$5:$J$1036,">="&LEFT(SUMMARY!D$3, SEARCH("", SUMMARY!D$3)1))
Hi Oscar, im just trying to change the format from 09/02/2012 to 2 Sept is it possible, ive tried format with no luck whatsoever, it just doesnt seem to take any change, even tried to change it to a percentage still no effect. looks like the formula is locked in a format style?