Jump to content


Photo

Date menu showing error icon for some reason

dates DATE date serial numbers custom formats error checking

  • Please log in to reply
3 replies to this topic

#1 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 21 September 2015 - 02:10 PM

I know this seems small, but if I'm going to rely on Excel to tell me when there's an error in a cell by showing me the diamond-shaped icon, there better be a good reason. Otherwise, it's just an annoyance.  :(

 

On the first tab of the attached file, I have one cell with a pull-down menu that's fed by a row of cells on the second tab.

 

The values are dates. I've done this a million times, but today Excel is telling me that there is an error in the cell with the menu. This would normally indicate it's not in line with other similar cells surrounding it with a different formula, but since there is no other formula, I feel like Excel is toying with me.

 

I have tried changing the formats of both the menu and source cells to no avail.

 

Any thoughts on why this is occurring and how to get rid of it?

 

Attached File  menu with error icon.xlsx   11.57KB   143 downloads



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 21 September 2015 - 04:52 PM

Hi phoenixrizing,

 

If you click the smart tag that appears to the left of the error, you can see Excel's complaint:

 

DateError.png

 

As you can see, Excel is complaining that you have a date in Text format that has a 2-digit year. Excel sees this as a problem because it is ambiguous whether 6/2/16 represents the 2nd of June 2016 or the 2nd of June 1916. As you can see, it is offering to convert the date into one of these two formats so that it will no longer be ambiguous.

 

You could eliminate the ambiguity by adding the full year to each of your dates, but the best solution to this would be to convert your textual dates into 'true' dates (ie. date serial numbers). A date serial number never has any ambiguity about which date it represents.

 

You'll remember that date serial numbers are explained in great depth in the video lesson: Expert Skills Lesson 3-7 Understand Excel date serial numbers and custom date formats.

You can also see how to use the DATE function to convert text into date serial numbers in the video lesson: Expert Skills Lesson 3-11 Use the Excel DATE function to offset days, months and years.

 

I have attached a copy of your workbook with the textual dates replaced with date serial numbers:

 

Attached File  menu with error icon datefix.xlsx   11.94KB   134 downloads

menu with error icon datefix.png

 

An alternative solution might be to switch off Background Error Checking, which will stop Excel from automatically displaying error icons. However, this will be saved with your copy of Excel and not with the workbook, meaning that it will affect all workbooks that you open with your copy of Excel. It may also cause errors to be hidden that you'd prefer to be alterted to, so I wouldn't recommend doing this.

 

If you do want to switch off Background Error Checking, you can see how to do it in the video lesson: Expert Skills Lesson 4-12 Manually check a worksheet for errors.

 

I hope this solves the problem, but please feel free to reply if you continue to have trouble.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 21 September 2015 - 05:51 PM

Date formatting: my archenemy.  :angry:
 
I understand what you did and why you did it, but here's where I get confused. My source data comes in as serial numbers. I reformat them into m/d/yy, but Excel still knows it's 2016.
 
So why does it forget it's 2016 when I pull those numbers into a list?


#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 888 posts

Posted 21 September 2015 - 10:53 PM

Hi phoenixrizing,

 

Your original workbook had the dates as text rather than as serial numbers, so I'm not sure what you mean. A serial number always knows which year it belongs to no matter how it is formatted, but text only contains as much information as you can see so Excel points out the ambiguity.

 

The example attached above seems to be working fine when using date serial numbers in a list validation, so I can only suggest taking a second look at your source data and making sure that it really does consist of serial numbers.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users