Date & Time Functions

This page lists the current, built-in Excel Date and Time Functions, which will assist you when working with Excel dates and times.

Before using Excel Date and Time Functions, it will help if you have an understanding of the way dates and times are stored in Excel - this is explained in the Excel Dates and Times page.

The functions listed below have been grouped into categories, to help you to find the function you need. Note that some of the functions are new to Excel 2010 or Excel 2013, so are not available in earlier versions of Excel. If you select a function name, this will take you to a full description of the function, with examples of use and common errors.

• AND

  Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise

• BAHTTEXT

  Converts a number, plus the suffix "Baht" into Thai text

• Basic Excel Formatting

  As well as giving your spreadsheet a professional look, the use of Excel formatting can provide essential information that determines the way a user interprets the data in the spreadsheet.

• CHAR

  Returns the character that corresponds to a supplied numeric value

• CLEAN

  Removes all non-printable characters from a supplied text string

• CODE

  Returns the numeric code for the first character of a supplied string

• Common Excel Keyboard Shortcuts

  Most people know some Excel keyboard shortcuts, and therefore recognise that they can be a great time-saver and can help you to work much more smoothly and efficiently.
  
  This page lists of the most popular Excel shortcuts. The shortcuts have been organised into categories, to help you to find those that are of particular interest to you. It is recommended that you try to remember a few at a time, and make use of these in your day-to-day work, until they become second nature to you.

• CONCATENATE

  Joins together two or more text strings

• Conditional Formatting

  Excel Conditional Formatting can be used to alter the formatting of an Excel cell based on either:

• Convert Excel To PDF

  Many users wish to convert Excel files to PDF ("Portable Document Format"). PDF files present your spreadsheet to others in a way that preserves the document's formatting and prevents the files from being easily altered.

• Create Hyperlink in Excel

  The use of hyperlinks in Excel can transform a spreadsheet into an entire filing system.

• DOLLAR

  Converts a supplied number into text, using a currency format

• ERROR.TYPE

  Tests a supplied value and returns an integer relating to the supplied value's error type

• EXACT

  Tests if two supplied text strings are exactly the same and if so, returns TRUE; Otherwise, returns FALSE. (case-sensitive)

• Excel Advanced Filter

  The Excel Advanced Filter can be used to perform more complex filtering than the basic Excel Autofilter.

• Excel Autofill

  The Excel Autofill feature can be used to populate a range of cells with either a repeat value,

• Excel AutoFilter (Basic Filter)

  The basic Excel filter (also known as the Excel Autofilter) allows you to view specific rows in an Excel spreadsheet, while hiding the other rows in the worksheet.

• Excel Cell References

  In Excel, you can use Relative References (references that change as they are copied to other cells) or Absolute References (references that remain constant if they are copied to other cells).
  
  These are discussed individually below.

• Excel Copy And Paste

  .

• Excel Custom Number Formatting

  How to Define an Excel Custom Number Format

• Excel Data Input

  The basic Excel task of populating a spreadsheet can be done in several different ways. These methods, which are described in the linked pages below, will help you to work more efficiently in Excel.

• Excel Dates and Times

  Most users have worked with Excel dates and times, to some extent. However, not all users fully understand the way that dates and times are stored within Excel, and this prevents them from getting the most out of their Excel functions and formulas.

• Excel Drop Down List

• Excel Reference Styles

  Excel understands two different styles of referencing for cells and ranges. These are described in the table below :

• Excel Zoom

• FALSE

  Simply returns the logical value FALSE

• FIND

  Returns the position of a supplied character or text string from within a supplied text string (case-sensitive)

• FIXED

  Rounds a supplied number to a specified number of decimal places, and then converts this into text

• Hide Gridlines In Excel

  The gridlines in an Excel spreadsheet are only shown when looking at the worksheet electronically,

• How to Subtract Dates in Excel

  If you want to subtract dates in Excel, it helps if you first understand how dates are stored within Excel.

• How to Subtract Time in Excel

• IF

  Tests a user-defined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE

• IFERROR

  Tests if an initial supplied value (or expression) returns an error, and if so, returns a supplied value; Otherwise the function returns the initial value.

• IFNA

  Tests if an expression returns the #N/A error and if so, returns an alternative specified value; Otherwise the function returns the value of the supplied expression

• Inputting Excel Function

  The most obvious way of inputting Excel is simply to type the function, with its arguments enclosed in brackets. However, if you are a beginner, or are writing a complex formula, you may find this difficult or confusing. Therefore, Microsoft has included a number of built-in Excel tools, which will help you to input functions and formulas more easily.

• Insert a New Line in an Excel Cell

  <ALT> Enter

• ISBLANK

  Tests if a supplied cell is blank (empty), and if so, returns TRUE; Otherwise, returns FALSE

• ISERR

  Tests if an initial supplied value (or expression) returns an error (EXCEPT for the #N/A error) and if so, returns TRUE; Otherwise returns FALSE

• ISERROR

  Tests if an initial supplied value (or expression) returns an error and if so, returns TRUE; Otherwise returns FALSE

• ISEVEN

  Tests if a supplied number (or expression) is an even number, and if so, returns TRUE; Otherwise, returns FALSE.

• ISLOGICAL

  Tests if a supplied value is a logical value, and if so, returns TRUE; Otherwise, returns FALSE

• ISNA

  Tests if an initial supplied value (or expression) returns the Excel #N/A error and if so, returns TRUE; Otherwise returns FALSE

• ISNUMBER

  Tests if a supplied value is a number, and if so, returns TRUE; Otherwise, returns FALSE.

• ISODD

  Tests if a supplied number (or expression) is an odd number, and if so, returns TRUE; Otherwise, returns FALSE.

• LEFT

  Returns a specified number of characters from the start of a supplied text string

• LEN

  Returns the length of a supplied text string

• LOWER

  Converts all characters in a supplied text string to lower case

• Merge Cells in Excel

  Merge Cells in Excel

• MID

  Returns a specified number of characters from the middle of a supplied text string

• N

  Converts a non-number value to a number, a date to a serial number, the logical value TRUE to 1 and all other values to 0

• NA

  Returns the Excel #N/A error

• NOT

  Returns a logical value that is the opposite of a user supplied logical value or expression
  (ie. returns FALSE is the supplied argument is TRUE and returns TRUE if the supplied argument is FALSE)

• NUMBERVALUE

  Converts text to a number, in a locale-independent way

• OR

  Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, or FALSE otherwise

• PROPER

  Converts all characters in a supplied text string to proper case (i.e. letters that do not follow another letter are upper case and all other characters are lower case)

• Remove Hyperlinks in Excel

  If you want to remove hyperlinks from one or more Excel cells, simply select the cells containing the hyperlinks and right-click on the selected cell(s), using the mouse.

• REPLACE

  Replaces all or part of a text string with another string (from a user supplied position)

• REPT

  Returns a string consisting of a supplied text string, repeated a specified number of times

• RIGHT

  Returns a specified number of characters from the end of a supplied text string

• SEARCH

  Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive)

• Show Formulas In Excel

  The following decribes how to show formulas in Excel, for a whole worksheet, or how to just show the formula in a single Excel cell.

• SUBSTITUTE

  Substitutes all occurrences of a search text string, within an original text string, with the supplied replacement text

• T

  Tests whether a supplied value is text and if so, returns the supplied text; If not, returns an empty text string.

• TEXT

  Converts a supplied value into text, using a user-specified format

• TRIM

  Removes duplicate spaces, and spaces at the start and end of a text string

• TRUE

  Simply returns the logical value TRUE

• UNICHAR

  Returns the Unicode character that is referenced by the given numeric value

• UNICODE

  Returns the number (code point) corresponding to the first character of a supplied text string

• UPPER

  Converts all characters in a supplied text string to upper case

• VALUE

  Converts a text string into a numeric value

• weft

  wtrwet

• Wrap Text in Excel

  There are many situations in which you may want to wrap text in Excel cells, so that you can improve the appearance of, or easily view long text strings.

• XOR

  Returns a logical Exclusive Or of all arguments