Solved How to enter dates in Excel 2010/

Dell / Inspiron 17r
May 8, 2018 at 16:37:11
Specs: Windows 10, 2.3/8
I am trying to enter dates in a column. I highlighted the colum and formatted it with 'Date xx/xx/xx'.
When I typed in 122556 and pressed <Enter>, it returned 07/18/35. I tried other valid dates and got similar strange returns, e.g., 091889 -> 07/31/51.

Thank you.
Brian W


See More: How to enter dates in Excel 2010/

Report •

#1
May 8, 2018 at 19:42:00
Check your Options, Advanced,
looks like you may have it set to use the 1904 date system

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
May 8, 2018 at 20:24:28
✔ Best Answer
There is nothing wrong with your system. Excel is returning the correct date for those values and format.

If you were expecting to see 12/25/56 and 09/18/89, then it appears that you don't understand how dates and date formatting in Excel works. We can fix that. ;-)

First, let's start with how dates are stored in Excel:

Every date and time is stored as a number. The integer portion is the Date and the decimal portion is the Time. The integer value represents the number of days since the beginning of Excel time, which is 1/0/1900. Therefore 1 is equivalent to 1/1/1900, 2 is 1/2/1900, etc. 43228 is May 8, 2018.

Add a decimal portion to the integer and you will get the time. 1.5 is 12 Noon on 1/1/1900. 1.75 is 1/1/1900 6:08:38 PM, etc. 43228.9619894676 is May 8, 2018 11:05:56 PM

OK, now let's talk about Date Formatting:

Date/Time formatting simply takes whatever you enter into a cell and displays that Date and Time in the format specified, assuming that it can.(Obviously you can't display a text string as a Date and Time just via formatting)

As an example, with your format of Date xx/xx/xx, if you entered 12/25/1956 or December 25, 1956 or Dec 25, 1956 or 20814, it would display 12/25/56. As long as it can convert whatever you entered into a date, it will use the format you specified.

So let's put those 2 parts together:

You formatted a cell as dd/mm/yy. You then entered a number, e.g 122556. Excel assumes that you want to know the date that occurs 122,556 days since the beginning of Excel time and that you want it formatted as dd/mm/yy. The actual date is July 18, 2235, which get displayed as 07/18/35 since that is how you set the formatting. You can grab calendars for 1900 through 2235 and count the days if you want to verify that. ;-)

091889 (actually 91,889) is July 31, 2151 which gets formatted/displayed as 7/31/51.

Make sense now?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#3
May 9, 2018 at 06:06:02
mmcconaghy,

Thank you for your suggestions. I checked and the 1904 date system was not checked.

Brian W


Report •

Related Solutions

#4
May 9, 2018 at 06:11:50
DerbyDad03,

Thank you for your explanation on how Excel stores and uses numbers and dates. Is it then possible to type in (without quotation marks") "05092018", press <Enter> and get "05/09/2018"?

Thank you.
Brian W


Report •

#5
May 9, 2018 at 06:28:27
Is it then possible to type in (without quotation marks") "05092018", press <Enter> and get "05/09/2018"?

Without the use of a Helper Column, you need to enter the slash character to get a date.
It can also be accomplished if you use a Macro.

MIKE

http://www.skeptic.com/


Report •

#6
May 9, 2018 at 07:32:49
There are various methods, but the main issue is that most of them don't actually return a value that Excel recognizes as a Date. Most of the methods use a Custom format that returns a number that looks like a date. In most cases, you have to use a formula in another cell to create a date that Excel will treat as a Date. Here is one example that describes the two step process to get Excel to return a usable Date:

https://www.pcmag.com/article2/0,28...

If you DAGS for 'entering dates without slashes', you'll get lots of hits. Some work well, some are inconsistent because of leading zeros. eg. The first 2 digits of 12151956 are 12, so the month is obvious. However, the first 2 digits of 05151956 will be seen as 51. That makes it difficult for even VBA to return consistent results.

There may be workarounds, so keep looking.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#7
May 9, 2018 at 09:06:49
DerbyDad03,

Thank you for your assistance. I tried various methods that I found but there was a problem when I entered a date from any month such as January as 01. So I will use one of the given DATE formats and type in “/“.

Thanks again.
Brian W


Report •

#8
May 9, 2018 at 10:17:35
You could do something like this...it might help if you have a lot of dates to enter at one time.

1 - Add a button to your sheet or the context menu of your mouse (right click menu) or your quick access tool bar and assign the macro shown below to the button
2 - Format your date cells anyway you want
3 - Select a cell(s) for your date(s)
4 - Run the macro from the button
5 - Enter your Month, Day and Year in the InputBox via the number keypad, hitting Enter after each value.

It's a simple one hand, minimal movement operation.

Sub LazyDays()

'Get Month, Day, Year, Exit if Canceled

 m = Application.InputBox("Enter Month")
  If m = False Then Exit Sub
  
 d = Application.InputBox("Enter Day")
  If d = False Then Exit Sub
  
 y = Application.InputBox("Enter Year")
  If y = False Then Exit Sub
  
 Selection.Value = m & "/" & d & "/" & y

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#9
May 9, 2018 at 10:51:06
DerbyDad03,

Thank you for your suggestion; I'll give it a try.

Brian W


Report •

#10
May 9, 2018 at 11:30:40
There are also several Date Picker add-ons that you might want to look at:

I have Sam Radakovitz Date Picker installed:

http://samradapps.com/datepicker/

Has a Button on the Ribbon, and also you can Right Click and select.

Ron de Bruin also has a version

https://www.rondebruin.nl/win/addin...

MIKE

http://www.skeptic.com/


Report •

#11
May 9, 2018 at 11:42:38
mmcconaghy,

Thank you for your suggestions; I'll give them a try.

Brian W


Report •

#12
May 10, 2018 at 14:25:39
DerbyDad03 and mmcconaghy,

Thank you for your suggestions. I think I’ll stay with using a “/“ with the dates.

Thanks again,
Brian W


Report •

Ask Question