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
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
December 28th, 2010 at 9:37 pm
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 ?
December 28th, 2010 at 10:15 pm
@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.