How to easily enter dates without slashes in Excel?
Generally, when we enter standard dates, we need to enter slashes to separate them, such as MM/DD/YYYY. But in some times, you may want to directly enter dates as sequence numbers, for instances, 01022016, and then the dates can be converted to standard dates formatting as 1/2/2016 as below screenshot shown. Here in this article, I introduce the tricks on easily entering standard dates without slashes in Excel.
Enter dates without slashes with Text to Column
In Excel, you can apply the Text to Columns feature to quickly convert 8-digits sequence numbers to standard dates.
1. Select the 8 digits numbers and click Data > Text to Columns. See screenshot:
2. In the step 1 of Convert Text to Columns Wizard, check Fixed width option, see screenshots:
3. Click Next > Next to go to the step 3 of the Wizard, and check Date option in Column data format section, and select MDY from next drop down list, and specify a cell as destination. See screenshot:
4. Click Finish. Now the sequence numbers have been converted to standard dates.
Tip: For correctly converting to dates, you can convert the numbers to text at first.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Enter dates without slashes with Format Cells function
Also, you can apply the Format Cells feature to convert the sequence number to standard date format.
1. Select the numbers to right click, and select Format Cells from context menu. See screenshot:
2. Then in the Format Cells dialog, under Number tab, click Custom from the Category pane, and then go to Type textbox to enter ##"/"##"/"#### in the right section. See screenshot:
3. Click OK. And the selected numbers have been converted to standard dates.
Enter dates without slashes with formula
IMoreover, you can apply formulas to convert numbers to date format.
Select a blank cell next to the numbers you want to use, enter this formula =DATE(RIGHT(A9,4),LEFT(A9,IF(LEN(A9) = 8,2,1)),LEFT(RIGHT(A9,6),2)), A9 is the number you want to convert, drag the autofill handle over the cells you want to apply this formula, see screenshot:
Enter dates without slashes with Kutools for Excel
If you have Kutools for Excel, you can apply its Convert to Date utility to quickly convert multiple non-standard dates to standard dates.
After free installing Kutools for Excel, please do as below:
Select the numbers you want to convert to dates, and click Kutools > Content > Convert to Date. See screenshot:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in