Allow date literals in functions
Many functions like VLOOKUP, SUMPRODUCT, and IF allow dates as inputs, but you need to know the tricks for doing so. The result is a never-ending series of questions in help forums from newbies who just don't understand why their formula isn't working.
If you enter 1/15/2015 in a formula, it is treated as the small number resulting from two successive divisions instead of a date. If you enter Jan 15, 2015 in a formula, it results in an error--and if you encapsulate it in double quotes "Jan 15, 2015" if is treated as a text literal. My current workaround is to enter dates as text prefixed by two minus signs: --"1/15/2015" which Excel will dutifully coerce into a date/time serial number. But that trick is not at all obvious to new users.
It would be nice if we could shorten the formula and enter the date directly. For example:
=VLOOKUP(1/15/2015, A2:F100, 3, FALSE)
=SUMPRODUCT((A2:A100>=1/1/2015)*(A2:A100<=1/31/2015),B2:B100)
=IF(A2=Jan 15, 2015,"Date exists", "Date not found")
Brad Yundt
shared this idea
4 comments
-
Kenneth Barber commented
I forgot to mention another problem that I have with introducing date literals: they represent hard-coded values. It would be better to keep the dates in separate cells, label them, and then reference them in formulas.
In response to Roy, saying that Excel can store date literals however it likes and display them in a default format is a long way of saying that dates need to be their own data type, treated differently from other numbers. I agree with that idea, but for different reasons. See my suggestion below:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/38778028-add-64-bit-unix-time-as-a-data-type -
The problem with using dates in strings is that you are depending on some sort of setting, which is not saved in the spreadsheet, for the string to be interpreted correctly as a date. This becomes a problem when the spreadsheet is shared, since not everyone will have the same settings set in Excel or in Windows, and different people will get different results for, say, "01-02-03". DATE avoids these issues because it forces the year, month, and day into a specific spot. I view DATE as the solution, not the workaround.
Also, if we really want to introduce dates as text in SUMIFS, you could simplify ">="&"1/1/2015"to ">=1/1/2015".
-
Hugo Hinterberger
commented
VBA already has date literals: Enclose the date with #, e. g. #2019-02-08# or #12:44#. I t would be nice and consistent to be able to use those in worksheet formulas.
-
The problem with dates is that there are so many ways to represent them. "Jan 15, 2015" isn't so bad, but no one types that either. Everyone likes their "12/09/05" or "12-9-5" format, which is of course the one that no one agrees on, and each region will argue that it's "obviously" yy-mm-dd, or dd-mm-yy, or mm-dd-yy, or whatever. Then there's "Jan-02". January 2 of this year or January 1 of 2002? You get the idea.
The least debatable and cleanest date representation is probably the DATE function. If a user is advanced enough to be using VLOOKUP, they can surely learn DATE. If you insist on some other format, try DATEVALUE, which converts your string to a date and is much clearer than --, albeit a bit longer.
Another thing to keep in mind is that, usually, the dates that we need are in other cells anyways, so just reference them. Hopefully you're not asking users to enter data into formulas directly. Have a data entry cell.
BTW, your SUMPRODUCT formula uses an obsolete technique. They introduced SUMIFS a while back, so now you can just go SUMIFS(B2:B100,A2:A100,">="&DATE(2015,1,1),A2:A100,"<="&DATE(2015,12,31)). Alternatively, give yourself a "year" helper column and just use regular SUMIF over the year.