How To Turn Off Auto Date In Excel

How to finish machine formatting number to appointment in Excel?

In Excel, when you type a cord number similar "2014-six-ane" or "ane/2" into a cell, information technology will auto format this number to date "half dozen/1/2014" or "2-Jan". Sometimes, you may think this car formatting is annoying, now I tell you how to end auto formatting number to date in Excel.

Stop auto formatting number by adding a space or apostrophe

Finish auto formatting number by formatting every bit Text

Convert text appointment/number cord to standard engagement

End auto formatting number by calculation a space or apostrophe

If you only have a few numbers to enter, you can stop Excel from changing them into dates past inbound a space or apostrophe.

Before you enter the number data, type a space or apostrophe ' into the cell then post-obit the number data, press Enter. If you type the space earlier data, the infinite volition be retained later on press Enter push, if you type the apostrophe, the cell format will be converted to text format, and the apostrophe will disappear when you printing Enter. See screenshots:

Finish auto formatting number past format equally Text

If you have a long list of number data need to be typed into cells and end motorcar formatting to appointment, you tin utilize the Format Cells function in Excel.

i. Select the range you desire to type the data number and correct click to open the context menu and click Format Cells. See screenshot:

doc-stop-auto-formating-date-3

two. Click Number tab and select Text from the Category listing. Then click OK to shut the dialog. Run into screenshot:

doc-stop-auto-formating-date-4

Now you tin blazon the number data into the range and it cannot exist automobile formatted every bit date. Run across screenshot:

doc-stop-auto-formating-date-5

Annotation: The above 2 methods cannot piece of work when the number data has been typed already.

Convert text date/number cord to standard date

When you re-create date from other sources to Excel, the date mayhap modify to text format, or for convinient, you lot may blazon engagement as number string directly, such equally 20160504. In thses example, yous may desire to convert thses nonstandard dates to the standard date formatting mm/dd/yyy y . Here with Kutools for Excel--a handy tool, its Catechumen to Date utility can chop-chop convert multiple non standard dates to standard dates.

After gratis installing Kutools for Excel, delight do as beneath:

ane. Select the nonstandard dates, and click Kutools > Content > Convert to Date. Run into screenshot:
doc stop auto formatting date 6

two. Later on applying the Convert to Date, you tin encounter all the nonstandard dates take been converted to the standared dates in the Convert to Date dialog, and just click OK to exist the dialog.
doc stop auto formatting date 7

Relative Articles:

Stop rounding numbers in Excel

The Best Function Productivity Tools

Kutools for Excel Solves Well-nigh of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and annihilation that you have used before; Encrypt Cells with countersign; Create Mailing Listing and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit big numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without irresolute formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Excerpt Text, Add together Text, Remove by Position, Remove Space; Create and Impress Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and utilise filter schemes to other sheets); Avant-garde Sort by month/week/mean solar day, frequency and more than; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split up Data into Multiple Sheets; Batch Catechumen xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Like shooting fish in a barrel deploying in your enterprise or organization. Full features xxx-day complimentary trial. threescore-mean solar day coin back guarantee.

kte tab 201905

Part Tab Brings Tabbed interface to Office, and Make Your Piece of work Much Easier

Enable tabbed editing and reading in Discussion, Excel, PowerPoint , Publisher, Admission, Visio and Project.
Open up and create multiple documents in new tabs of the aforementioned window, rather than in new windows.
Increases your productivity past 50%, and reduces hundreds of mouse clicks for you lot every mean solar day!

officetab bottom