0

I have cells with dates in Excel. Some dates have dd-mm-yy (e.g. 20-01-15), others just have mm-yy (e.g. 01-15). I want to determine which cells have a day specified by the user.

As far as I can tell, this isn't possible - when the user doesn't type a day of the month, excel's underlying date object stores the first day of the month. However, the day isn't shown in the cell, so there must be a flag or setting somewhere for this.

If I could extract the displayed cell text then I could parse this to answer my question, but I can't find a worksheet function to extract displayed cell text without using VBA.

  • You have to be really careful here. In the U.S. formatting scheme, if a user just enters a date in the mm/yy format, and the two-year date value yy falls between 1 and 31, Excel actually interprets it as an mm/dd value in the current calendar year. (e.g., 12-14 is interpreted as 14 Dec 2015, not as 1 Dec 2014.) If yy is zero or greater than 31, it will interpret mm/yy as intended. – hBy2Py Jul 30 '15 at 16:25
  • Beware also the dd-mm-yy format, as it might behave unexpectedly on you, too. – hBy2Py Jul 30 '15 at 16:27
  • Can you clarify your objective? Is it what you ask in your 1st paragraph: determine which cells have a day specified by the user? Or, have you already determined that this is not possible (2nd paragraph), and you are looking for a way to display the actual user entry (or something else)? The mechanism you describe in the 2nd paragraph isn't how the dates are stored (the stored value is the number of days since a reference date; the display is just formatting). – fixer1234 Jul 30 '15 at 17:36
2

If the user enters a date, they can hide the day or the month or the year by formatting. But this is only how the cell is displayed. If, however, you look in the Formula Bar; the day will be visible.

enter image description here

If your only interest is to determine which of the two formats the user has selected, (and not the actual day-of-the-month), the use the =Cell() worksheet function.

  • It took me forever to figure out how to track down reference info on Microsoft's site. I figure I'd save people the trouble. – hBy2Py Jul 30 '15 at 16:35
0

You can stop Excel from automatically inserting the first day of the month by formatting the cell as Text. This does have to be done before the data is input though.

0

Your question is a bit ambiguous. I'll address what I think you're asking.

What is stored

If you enter something that Excel interprets as a date, it stores it as a date, rather than the actual character string that was input. A date is stored as a number that represents the number of days since a reference date. So the stored value is actually a day count, it doesn't contain or not contain a day depending on whether one was entered.

Everything else (year, month, day of the month, etc.), is derived from that, and the format specification determines what details are displayed. If a month/year date is entered, the only way to store it as a date for it to be a specific date, so Excel assumes the 1st of the month.

Cell query not useful

If Excel interprets an input as a date, the actual input is no longer there to analyze to determine what the user typed (so a function to display the cell text won't help).

However, you might be able to assume what was entered in certain circumstances. I did a quick test in Excel 2007 (I assume this behavior didn't change in subsequent versions).

  • If Excel assigns a day of the month (because none was entered), it will be the 1st of the month. If all dates for a user are the first of the month and if there is no reason why that should always be the case (i.e., the dates are not defined as always being the 1st), that would be an indication that only month and year were entered.

    Conversely, if any dates displayed as month/year contain a day that is not the 1st (check by referencing the cell from another cell and display mm/dd/yy there), that would indicate that the user entered the day and it is being suppressed by the formatting. It is unlikely that a user would enter the day only on dates other than the 1st of the month.

  • If the cell is preformatted with a date format, date inputs will be displayed in that format. If the cell has a general format, a date input will be displayed in a default date format that shows the same level of information. So, for example, when I typed 07/15 as input to a cell with a general format, it displayed 15-Jul; not identical to what I typed but the same level of information.

    If the date input has mixed formats, that would indicate that the cells were not pre-formatted. So dates showing only month and year are likely to have been entered as just month and year.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.