Create a date range using excel formula
Question: I am trying to create an excel spreadsheet that has a date range.
Example: Cell A1 1/4/09-1/10/09
Cell B1 1/11/09-1/17/09
Cell C1 1/18/09-1/24/09
How do I create a formula to do this?
Answer: See row 3 and formula below picture.
Formula in A3:
Formula in A6:
Formula in A9:
Formula in A18:
How to use formulas in your workbook
Copy the formula to your sheet. To change start date, change bolded values in the above formula.
How the formula in cell A3 works
The goal is to create a date range from sunday to saturday.
When cell A3 is copied and pasted to the right, the date range adjusts.
The formula consists of two parts. Start date and end 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 start date
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
Functions used in this blog post:
DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code
TEXT(value, format_text)
Converts a value to text in a specific number format
ROW(reference) returns the rownumber of a reference
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
October 22nd, 2009 at 6:01 pm
how can i get the date range as : 01/01/09 - 01/15/09, 01/16/09 - 01/31/09 and so on
October 23rd, 2009 at 9:13 am
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: http://www.get-digital-help.com/2009/11/04/create-a-custom-date-range-in-excel/
April 22nd, 2011 at 7:18 pm
this isn't working when i copy and paste?
April 23rd, 2011 at 7:17 am
clay,
There seems to have been a typo.
You may also need to adjust mm/dd/yy depending on your regional settings.
http://www.techonthenet.com/excel/formulas/text.php
Thanks for commenting!
June 8th, 2011 at 5:45 pm
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 B3-B14. 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/11-6/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?
June 9th, 2011 at 8:47 am
Shannon,
read this post: Date ranges for 7 days in excel
Thank you for commenting!
August 19th, 2011 at 4:37 pm
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?
August 22nd, 2011 at 12:32 pm
todd,
Right click and hold on black dot.
Drag down and release right mouse button. Click Fill series.. with left mouse button.
August 22nd, 2011 at 8:33 pm
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/11-3/31/12 then enter 1 in Column M, if not enter 0 in Column M.
I hope you can help.
Thanks
August 23rd, 2011 at 7:47 am
Michelle,
Formula in cell M1:
August 23rd, 2011 at 4:18 pm
perfect thank you Oscar
August 24th, 2011 at 1:02 pm
Michelle,
You are welcome!
September 21st, 2011 at 8:26 pm
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")
September 22nd, 2011 at 9:49 am
DanL,
Thanks! I have updated this post.
October 13th, 2011 at 9:53 pm
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
October 17th, 2011 at 8:29 am
Richelle,
can you describe your question in greater detail?
Also add some example data and what you are trying to achieve?
October 17th, 2011 at 7:10 pm
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...
October 19th, 2011 at 8:45 am
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?
October 19th, 2011 at 11:10 pm
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.
October 21st, 2011 at 10:13 am
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
October 25th, 2011 at 4:28 pm
WHIMIS and H/R are Blank.
October 26th, 2011 at 3:10 pm
Richelle,
Download file:Richelle.xls
October 27th, 2011 at 6:38 pm
Not quite, when the dates are added it does not return to normal.
October 28th, 2011 at 11:35 am
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?
October 28th, 2011 at 12:20 pm
Richelle,
Download file: Richelle1.xls
October 28th, 2011 at 5:53 pm
You ROCK!
Thank you so much!
October 31st, 2011 at 9:27 am
Shirley,
What are you trying to do?
Create dates with 11.58 years between them?
or
Calculate years between two dates?
October 31st, 2011 at 9:33 am
Calculate years between two dates.
October 31st, 2011 at 10:00 am
Shirley,
Is 0.58 a decimal fraction of a year?
I get 0.62 in my calculations.
October 31st, 2011 at 10:23 am
Oscar,
I think yours is the correct one as i got 0.58 from my system which I found not accurate.
November 2nd, 2011 at 2:35 pm
Shirley,
Formula in cell c4:
=TEXT(A4, "DD/MM/yyyy")&" to "&TEXT(B4, "DD/MM/yyyy")&" = "&ROUND((B4-A4)/365, 2)
This formula does not take leap years into account.
Excel dates: http://www.cpearson.com/excel/datetime.htm
November 3rd, 2011 at 11:35 am
thank you Oscar! i got the solution.
November 22nd, 2011 at 9:32 am
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.
December 13th, 2011 at 4:07 pm
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.
December 14th, 2011 at 6:04 pm
Came up with a solution - make a true/false date range column and pulled the data from that. Thanks anyway, learned a lot!
December 15th, 2011 at 8:33 am
How can I create a data range for different data apart from dates- say personnel ID #s? the formula given here is for dates..
December 20th, 2011 at 10:03 pm
Ian,
Can you give me some examples of what you want to achieve?
December 21st, 2011 at 4:59 am
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
December 21st, 2011 at 8:29 pm
Hi Oscar,
Kindly disregard my previous message.
I think I've got it.
I've used LOOKUP function.
Thanks though,
- oui
December 23rd, 2011 at 4:45 am
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 2-6, 2012.
Thanks,
Matt
January 2nd, 2012 at 12:43 pm
Here is a solution to oui´s question:
Learn more: Return value if in range in excel
January 2nd, 2012 at 2:35 pm
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
January 9th, 2012 at 3:21 pm
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
17-Jun-11
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
January 13th, 2012 at 10:18 am
Ingrid,
Date in cell I2: 17-Jun-11
Formula in cell J2:
January 13th, 2012 at 5:13 pm
This is awesome! THANK YOU!-ij
January 16th, 2012 at 6:11 am
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?
January 20th, 2012 at 1:51 pm
Chaks,
I hope you´ll find an answer here:
http://www.rondebruin.nl/weeknumber.htm
or here:
http://www.cpearson.com/excel/WeekNumbers.aspx
When does week1 begin? There are few possible ways to number weeks. See links.
January 25th, 2012 at 8:06 pm
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.
January 30th, 2012 at 3:03 pm
Ricardo,
It is possible!
Download excel file:
Ricardo.xls