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

Data Analysis

  • How To Create Frequency Distribution in Excel
  • Data Series in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Filter Data Based on Date in Excel
  • How to Create Area Chart in Excel

References

  • How to get address of last cell in range in Excel
  • How to use Excel INDIRECT Function
  • How to reference named range different sheet in Excel
  • Last row number in range
  • Convert text string to valid reference in Excel using Indirect function

Data Validations

  • Excel Data validation whole percentage only
  • Excel Data validation must begin with
  • Excel Data validation allow uppercase only
  • Excel Data validation with conditional list
  • Excel Data validation number multiple 100

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:   Convert date to month and year 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:   Convert date to Julian format 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:

Convert decimal hours to Excel time

Next Post:

Convert date to month and 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
  • Return blank if in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • How to use Excel AND Function

Date Time

  • Count day of week between dates in Excel
  • Count times in a specific range in Excel
  • Basic timesheet formula with breaks in Excel
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Display the current date and time in Excel

Grouping

  • If cell contains one of many things in Excel
  • Calculate conditional mode with criteria in Excel
  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel
  • Map inputs to arbitrary values in Excel

General

  • Excel Ribbon Quick Overview For Beginners
  • How to calculate percent sold in Excel
  • List sheet names with formula in Excel
  • Subtotal by invoice number in Excel
  • How to set or clear a print area in Excel Worksheet
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning