Excel changing dates

Question

How do I get Excel to take an 8 digit number--like 06222020--and convert it automatically to 06/22/2020 or 6/22/20 WITHOUT typing in the slashes? This is my preferred way to put in dates, but I have tried this too:

When I have a cell set to date and type something like 062220 (6-digit date) it auto corrects it to 5/7/2070. Where the heck is it getting THAT date from what I'm typing in?

Answer 1

Dates in Excel are a deep subject, with several layers of complexity.

.

In Excel, dates are a series number that starts at Jan 1, 1900, counting the number of days from then.

So Excel cannot handle dates before that day 'gracefully'

.

The serial number is meaningless to people, so Excel applies formatting to control the way dates are displayed to the user. Excel starts by using Windows System dates. If a user enters values that look like the "long" or "short" system dates, Excel converts the input to date format. You can change the way data is displayed by applying a Custom Date format.

.

The problem with the way Excel recognizes dates is that 8 digits, 8 number with no separators or spaces is not recognizable as a date. So Bernie's suggestion of using a macro sounds like one option. Another option would be to input your 8 numbers, then import it into PowerQuery and use it to convert the number to a date for you. If you are interested in a PQ solution, let us know. We can walk you through it.

Here is some general information about date handling in Excel and some articles about converting text or numbers to a date format that Excel can recognize.

.

The first question to answer is, do you want to use these "8 digit" strings you enter to be recognized as dates in Excel, or do you just want to have the slashes input into a text value that looks like a date, but is only text in Excel.

.

.

Format a date the way you want
https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
When you enter some text into a cell such as "2/2", Excel assumes that this is a date and formats it according to the default date setting in Control Panel. Excel might format it as "2-Feb". If you change your date setting in Control Panel, the default date format in Excel will change accordingly. If you don’t like the default date format, you can choose another date format in Excel, such as "February 2, 2012" or "2/2/12". You can also create your own custom format in Excel desktop.
.  *  Choose from a list of date formats
.  *  Create a custom date format
.  *  Tips for displaying dates
.
Custom Date / Time format codesEvery “Date” has implicit time attached, and every “Time” has implicit date attached.
Date CodesThe letters d, m, and y are used to represent days, months, and years in Excel date formats.
Date Mask    Displays.  d                days (1-31) without a leading zero
.  dd              days (01-31) with a leading zero
.  ddd            days (Sun-Sat) using 3-letter abbreviations
.  dddd          days (Sunday-Saturday) full day spelled out
.  m               months (1-12) without a leading zero
.  mm            months (01-12) with a leading zero
.  mmm         months (Jan-Dec) using 3-letter abbreviations
.  mmmm      months (January-December) full month spelled out
.  mmmmm   months (J-D) the first letter of the month
.  yy               years (00-99) last two-digits of year
.  yyyy            years (1900-9999) all four-digits of the year
.
Personally, I'd avoid the mmmmm format. How can you (or anyone else reading your worksheet) distinguish between January, June, and July, or between March and May, or April and August when looking at dates individually?
.
Time CodesThe letters h, m, and s are used to represent hours, minutes, and seconds in Excel time formats. Additionally, you can specify elapsed time and AM or PM in a custom format -
Time Mask         Displays.  h                        hours (0-23)   without a leading zero
.  hh                      hours (00-23) with a leading zero
.  m                       minutes (0-59)   without a leading zero
.  mm                    minutes (00-59) with a leading zero
.  s                        seconds (0-59)  without a leading zero
.  ss                      seconds (00-59) with a leading zero
.  [h]:mm              elapsed time in hours      (such as 28:30), Optional hours
.  [mm]:ss             elapsed time in seconds   (such as 65:20), optional Minutes
.  [ss]                    elapsed time in seconds (such as 90)
.  ss.000                Decimal seconds to thousands
.  [h]:mm:ss.000    Decimal Seconds to thousands, optional hours
.  h:mm AM/PM     hours (such as 6:00 AM or 6:45 PM)
.  h:mm A/P           hours (such as 6:00 A    or 6:45 P)
.
NOTE:
.   When a time format does not contain AM/PM or A/P the display is on the military 24hr clock.
.   If the format contains AM or PM, the hour is based on the 12-hour clock.
.      "AM" or "A" indicates times from midnight until noon; 12 AM is Midnight.
.      "PM" or "P" indicates times from noon until midnight; 12 PM is Noon.
.
Sample Custom Date And Time CodesThese are just a few examples to give you some ideas of what is possible with custom date and time formats. To display text next to a format code, enclose the text in quotes.

CODE                                  DISPLAYS.  d mmm yyyy                     3 Apr 2000
.  mmm/dd/yyyy                   Apr/03/2000
.  mmmm dd, yyyy (ddd)      April 3, 2000 (Mon)
.  hh:mm "GMT"                  14:15 GMT
.  [h]:mm "elapsed hrs"        28:30 elapsed hrs
.  h A/P                                8 A
.  [blue]dd-mm-yyyy             03-04-2000  (displayed in blue text)
.

!   Date and Time Intro         2020 10 17   Mynda Treacy
https://www.myonlinetraininghub.com/excel-date-and-time
The objective of this post is to teach you how Excel handles date and time and provide you with all the tools you will need.
It’s designed to be read in conjunction with the accompanying Excel file, which you can download.
.  *  Windows Regional Settings
.  *  Excel Date and Time 101
.  *  Dates
.  *  Time
.  *  Date & Time Together
.  *  Entering Dates
.  *  Entering Times
.  *  Entering Dates & Time together
.  *  Adding/Subtracting Days from Dates
.  *  Subtracting Dates from one another
.  *  Adding Times to one another
.  *  Subtracting Time from Times
.  *  Subtracting Times from one another
.  *  Date and Time Shortcuts keys
.  *  'Good to Know' Stuff about Excel Date and Time
.  *  Date Modes: 1904 vs 1900
More links:
.  *  Every Excel Date and Time Function explained
.  *  Formatting Date and Time in Excel
.  *  Common Date and Time Calculations
.

Format a date the way you want
https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e
When you enter some text into a cell such as "2/2", Excel assumes that this is a date and formats it according to the default date setting in Control Panel. Excel might format it as "2-Feb". If you change your date setting in Control Panel, the default date format in Excel will change accordingly. If you don’t like the default date format, you can choose another date format in Excel, such as "February 2, 2012" or "2/2/12". You can also create your own custom format in Excel desktop.
.  *  Choose from a list of date formats
.  *  Create a custom date format
.  *  Tips for displaying dates
.
Format numbers as dates or timeshttps://support.microsoft.com/en-us/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309
When you type a date or time in a cell, it appears in a default date and time format. This default format is based on the regional date and time settings that are specified in Control Panel, and changes when you adjust those settings in Control Panel. You can display numbers in several other date and time formats, most of which are not affected by Control Panel settings.
.  *  Display numbers as dates or times
.  *  Create a custom date or time format
.  *  Tips for displaying dates or times
.
Formatted Dates Appear Differently on Different Systems
https://excelribbon.tips.net/T009513_Formatted_Dates_Appear_Differently_on_Different_Systems.html
When you format a date in a specific manner, you may be surprised to see that the format changes when you open the workbook on a different system. There is a reason for this change, as described in this tip.
.

!  Quick Tips for Dates in Excel              2020 12 22
https://www.excelcampus.com/tips/quick-tips-dates/
Dates can be a challenge to work with in Excel. Especially when it comes to the date data type and making sure Excel recognizes your data as a date instead of text.
So this post covers a few quick tips for inserting and working with dates in Excel. There are links throughout the post to articles with additional details.
.  1. Inserting Dates in Excel
.      *  Static Dates
.      *  Static Date / Times
.      *  Dynamic Dates
.  2. Calculating Dates
.  3. Check Date Data Types- Display Home tab > Number group > Data type dropdown allows you to view data in various types
.

! Date Format in Excel
https://www.excel-exercise.com/date-format-in-excel/
In Excel, you can display the same date in many different ways just by changing the date format.
.  *  Dates are whole numbers
.  *  Explanation of the numbering
.  *  Short Date Format
.  *  Long Date Format
.  *  Customize your date format
.  *  Date parameters
.  *  How to customize a date?
.  *  Don't write text in your date
.  *  Different displays in function of the format code
.  *  Different examples of custom date
.

@ Change & Convert UK Dates to US – Change Long / Short Date Formatshttps://www.myexcelonline.com/blog/convert-uk-dates-to-us/
https://www.myexcelonline.com/wp-content/uploads/2018/02/Support-European-Date-Format-in-Excel-1.gif
Have you ever come across a scenario where your dates in Excel are in the wrong format?
Say you type in 01/05/2018 which actually means January 5, 2018 in the US but it shows as May 1, 2018!  It drives me nuts because the entire spreadsheet of dates is interpreted incorrectly by Excel!
That happens because your computer’s region settings are in UK format and need to be changed to a US format.

@ Changing to a Non-US Date Formathttps://excelribbon.tips.net/T013649_Changing_to_a_Non-US_Date_Format.html
Do you want to specify your months and days differently when displaying dates in your worksheets? This tip looks at how you can get the format you really want.
.

@ Convert date format between European and US in Excel?
https://www.extendoffice.com/documents/excel/4162-excel-convert-european-date-to-us-date.html
If you work in an international company, you will receive sheets from different counties every day which may record dates with different date formats. For example, the European counties record dates in the date format dd/mm/yyyy, while US record dates in the date format mm/dd/yyyy. In this case, how can you quickly convert the date format between European countries and US in Excel?
.  *  Convert texted date from European dd/mm/yyyy to US mm/dd/yyyy with formula
.  *  Convert date format between European and US with Format Cells
.

Convert between date and Unix timestamp in Excel?
https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html
Unix timestamp is also called Epoch time or POSIX time which is wildly used in many operating systems or file formats. This tutorial is talking about the conversion between date and Unix timestamp in Excel.
.  *  Convert date to timestamp
.  *  Convert date and time to timestamp
.  *  Convert timestamp to date
.  *  More tutorials about datetime conversion...
.

Answer 2

Hi,

In cell B2, enter this formula and copy down

=1*(TEXT(A2,"00/00/0000"))

Format the entries in column B as Dates.

Hope this helps.

Answer 3

Excel counts days from the turn of the last century - 1 is 1/1/1900, and 62220 days from then is 5/7/2070.

There are a lot of examples of VBA code in change events that allow this sort of thing - you just have to be consistent in your entry - is 12320 1/23 or 12/3?

Just follow the instructions at http://www.cpearson.com/excel/DateTimeEntry.htm