Search code examples
excelexcel-formuladynamic-arrays

Add in dates as a literal in a range

To add in numbers in an array I can do:

={1,2,3}

To add in text in an array I can do:

={"a","b","c"}

How would I enter in a date? For example, normally I can type in something like 1/1/07 in a cell and it will recognize it as a date. How would I enter that in into an array, such as:

={11/21/86, 11/1/14}

enter image description here

Solution

  • Use the DATE function combined with HSTACK as follow:

    =HSTACK(DATE(2022,1,14), DATE(2022,5,20))

    the array form {} is only for constants (literals). If you want the information in date format this is one way of doing it. Another way is to use string literals and then convert it to a date format as follows:

    =DATEVALUE({"2022/1/14","2022/5/20"})

    or

    =DATEVALUE({"14-Jan-2022","20-May-2022"})

    In both cases, the date should be in the following range: from January 1, 1900, to December 31, 9999. For more information, you can check the documentation of DATEVALUE. You can also use VALUE instead of DATEVALUE for the same purpose.

    Similarly, you can rely on Excel date cast:

    =HSTACK("1/14/2022"+0,"5/20/2022"+0)

    All previous solutions except the first one depend on your local/system date settings.

    Here is the output: output Note: The formulas were generated via FORMULATEXT, for example, FORMULATEXT(C1) and drag it down. The values in columns C and D numerically represent the corresponding dates.

    As it was pointed out in comment section by @EitzenRob. Only if you know the numeric representation of your date, i.e. the number of days since Jan,1,1990, then you can use:

    ={44575,44701}