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:

1. Create a quartely date range in excel
2. Create a monthly date range in excel
3. Create a date range using excel formula
4. Advanced custom date filter in Excel 2007
5. Create a unique distinct list from a date range in excel
6. Create a list of dates with blanks between quarters in excel
7. Formula for matching a date within a date range in excel
8. Create unique distinct year and months from a long date listing in excel
9. Lookup two index columns using min max values and a date range as criteria
10. Extract dates and adjacent value in a range using a date critera in excel