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:

bi-weekly date range

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: