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
6 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 -
Roy
commented
The point is that Excel knows your system settings. So if you use a date literal, marked somehow so Excel doesn't cause more trouble overdoing things (perhaps with the double "double quotes"), Excel would convert it whenever calculating the cell (like it would, say, "123.00" becoming "123" in the cell) and replace it internally with a date number...
Using the default date format to interpret it.
From then on, it would show as what you entered but exist in Excel as the date number it prefers. The value Excel stores and uses would be analogous to normal cell formatting while the literal it SHOWS you in the cell would be analogous to in-cell conditional formatting.
(They might even do it literally that way. Then it could be extended to all our other complaints about changing entered values, like the example above of "123.00" becoming "123" — we don't have memory and storage issues like in 1988 anymore.)
Since it would be the date number (like "43208") to Excel, it would then show the user that date as a literal, but in the format that is THAT user's default format. No misinterpretations on Excel's part.
Easy. And all those other issues that would be resolved by extending the use of CF-ing into in-cell material. (For Excel's use only, I do not picture extending it in a way that users could access it directly.)
To recap again (I know, but this isn't 10th grade Composition and I am not a professional writer), Excel would immediately change the actual value being stored to it's own preferred whatever, probably the "43208" kind of value, show all users the date literal in the formula in their own default format, and use its own version when appropriate (most things).
It'd probably need to make users extract portions of it via things like MONTH() rather than string functions so it didn't need to make a lot of decisions and store a vastly larger amount of information. But we'd understand that. Some might grouse, but they'd understand it. (Until 20-30 years from now when someone would say something about not being limited in storing or using those vast amounts of extra information like I did above...)
-
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".
-
Roy
commented
Yes, even if we had to double "double quote" them, it would sure be nice... easier to type and easier to understand than something like ="1/15/2015"+0. (Or the "--" of course, any arithmetic operation will do. I'm used to the "+N("text comment you want in the formula")" so I usually add a zero rather than the "--" approach, but any arithmetic operation that doesn't change its value works.)
People are used to "+0" or some othe number being used for offsets (like in ROW()-1 to account for a header row) so what I do just confuses them (which "--" does also). I'm all for having none of that anymore and this idea would not only eliminate one whole kind of source, but make for easier to formulas.
As to Mr. Barber's comment, no, not a problem. First, one could picture this working with numerical literals that are formed following the system settings for the computer (unless overridden in Excel's settings) using numerals. So two legs good, four legs bad: 1/15/2015 good, Jan 15, 2015 bad. Second, if Excel can recognize a form as one types it into a cell, then it ought to be able to use the same programming to deal with a marked (like the double "double quotes" thought) date literal.
I wonder why Mr. Barber's SUMIFS() uses the Date() functions when a literal would work just fine:
">="&"1/1/2015"
Point of all of this really, is we want to suggest improvements, not live with second best and workarounds. So...
-
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.