Create a custom date range in excel
Question: how can i get the date range as : 01/01/09 - 01/15/09, 01/16/09 - 01/31/09 and so on
Answer:
Array formula in A4:
=TEXT(IF(MOD(COLUMN(A:A), 2), DATE(2009, ROUND(COLUMN(A:A)/2, 0), 1), DATE(2009, ROUND(COLUMN(A:A)/2, 0), 16)), "MM/DD/YY")&"-"&TEXT(IF(MOD(COLUMN(A:A), 2), DATE(2009, ROUND(COLUMN(A:A)/2, 0), 15), DATE(2009, ROUND(COLUMN(A:A)/2, 0)+1, 1)-1), "MM/DD/YY") + ENTER
copied rigt as far as needed.
Array formula in A7:
=TEXT(IF(NOT(MOD(COLUMN(B:B), 2)), DATE(2009, 2-ROUND(COLUMN(B:B)/2, 0), 1), DATE(2009, 2-ROUND(COLUMN(B:B)/2, 0), 16)), "MM/DD/YY")&"-"&TEXT(IF(NOT(MOD(COLUMN(B:B), 2)), DATE(2009, 2-ROUND(COLUMN(B:B)/2, 0), 15), DATE(2009, 2-ROUND(COLUMN(B:B)/2, 0)+1, 1)-1), "MM/DD/YY") + ENTER
copied rigt as far as needed.
Array formula in A10:
=TEXT(IF(MOD(ROW(1:1), 2), DATE(2009, ROUND(ROW(1:1)/2, 0), 1), DATE(2009, ROUND(ROW(1:1)/2, 0), 16)), "MM/DD/YY")&"-"&TEXT(IF(MOD(ROW(1:1), 2), DATE(2009, ROUND(ROW(1:1)/2, 0), 15), DATE(2009, ROUND(ROW(1:1)/2, 0)+1, 1)-1), "MM/DD/YY") + ENTER
copied down as far as needed.
Array formula in A18:
=TEXT(IF(NOT(MOD(ROW(2:2), 2)), DATE(2009, 2-ROUND(ROW(2:2)/2, 0), 1), DATE(2009, 2-ROUND(ROW(2:2)/2, 0), 16)), "MM/DD/YY")&"-"&TEXT(IF(NOT(MOD(ROW(2:2), 2)), DATE(2009, 2-ROUND(ROW(2:2)/2, 0), 15), DATE(2009, 2-ROUND(ROW(2:2)/2, 0)+1, 1)-1), "MM/DD/YY") + ENTER
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
Related articles:
- Create a weekly date range using excel formula
- Create a quartely date range in excel
- Create a monthly date range in excel
Related posts:
Create a monthly date range in excel
Create a quartely date range in excel
Count and visualize specific weekdays in a custom date range
Recommended categories
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?