Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Create Pareto Chart in Excel
  • Filter Data Based on Date in Excel
  • Error Bars in Excel
  • Get column index in Excel Table
  • How to sum a total in multiple Excel tables

References

  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Extract data with helper column in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Count rows with at least n matching values

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation only dates between
  • Excel Data validation number multiple 100
  • Excel Data validation specific characters only
  • Excel Data validation must begin with

Convert date to text in Excel

by

This tutorial shows how to Convert date to text in Excel using example below.

If you need to convert dates to text (i.e. date to string conversion) , you can use the TEXT function. The TEXT function can use patterns like “dd/mm/yyyy”, “yyyy-mm-dd”, etc. to convert a valid date to a text value. See table below for a list of available tokens.

 Formula

=TEXT(date,format)

Explanation

How this formula works

Dates and times in Excel are stored as serial numbers and converted to human readable values on the fly using number formats. When you enter a date in Excel, you can apply a number format to display that date as you like. In a similar way, the TEXT function allows you to convert a date or time into text in a preferred format. For example, if the date January 9, 2000 is entered in cell A1, you can use TEXT to convert this date into the following text strings as follows:

=TEXT(A1,"mmm")            // "Jan"
=TEXT(A1,"dd/mm/yyyy")     // "09/01/2012"
=TEXT(A1,"dd-mmm-yy")      // "09-Jan-12"

Date format codes

Worked Example:   Pad week numbers with zeros in Excel

Assuming a date of January 9, 2012, here is a more complete set of formatting codes for date, along with sample output.

Worked Example:   Create date range from two dates in Excel
Format code Output
d 9
dd 09
ddd Mon
dddd Monday
m 1
mm 01
mmm Jan
mmmm January
mmmmm J
yy 12
yyyy 2012
mm/dd/yyyy 01/09/2012
m/d/y 1/9/12
ddd, mmm d Mon, Jan 9
mm/dd/yyyy h:mm AM/PM 01/09/2012 5:15 PM
dd/mm/yyyy hh:mm:ss 09/01/2012 17:15:00
Worked Example:   TEXT function: Description, Usage, Syntax, Examples and Explanation

You can use TEXT to convert dates or any numeric value in a fixed format. You can explore available formats by navigating to Format Cells (Win: Ctrl + 1, Mac: Cmd + 1) and selecting various format categories in the list to the left.

Post navigation

Previous Post:

Get day from date in Excel

Next Post:

Calculate loan interest in given year in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function
  • TRUE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert date to text in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Calculate years between dates in Excel
  • TIME function: Description, Usage, Syntax, Examples and Explanation
  • Basic Overtime Calculation Formula in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • If cell contains one of many things in Excel
  • Group numbers with VLOOKUP in Excel
  • Categorize text with keywords in Excel
  • Group times into 3 hour buckets in Excel

General

  • Sum by group in Excel
  • How to make excel worksheets print on one page?
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • Select, Insert, Rename, Move, Delete Worksheets in Excel
  • With vs Without Array Formula in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning