Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles format date and time How to format date and time in Excel – Excelchat

How to format date and time in Excel – Excelchat

Excel has many options that can help us display dates and time in any way we want apart from the default serial format. This post provides a step by step guide on how to display dates and time in a variety of ways.

Dates

In Excel, date are stored as integers that represent the number of the days since January 1, 1990. Notice that January 1, 1990 is stored as number 1. In Excel, you will find that 2 is January 2, 1990, 3 is January 3, 1990 and so on.

When it comes to dates, Excel stores them as decimals.  The decimals range between .0 and .99999. The decimals represent a portion of the day. Notice also that .0 represents 00:00:00 and .00000 is 23:59:59

From the above explanation, it is easy to see that Excel stores dates and times as decimal numbers that has an integer for date and decimal portion for time.

Converting date to time

There are two ways through which one can know which serial number represent which date or time.

1: Formatting cells dialog

The first way to know which date or time is represented by a given serial number is by simply formatting the cells dialog. To do this simply select the cell that has the date and press Ctrl + 1. This will open the Format cells window and switch to the General tab.

In the general tab window you will be able to see the serial number behind the date. If you want to format date to show the actual date, then click OK. But if you just wanted to see the serial number, then click Cancel.

Figure 1: Viewing Excel date value

2: Using DATEVALUE and TIMEVALUE functions

  • We can also use the DATEVALUE function to convert a date to a serial number.
    =DATEVALUE ()
  • If we want to get a decimal that represents a given time, then we can utilize the TIMEVALUE function.
    =TIMEVALUE ()

Changing default date and time formats

In Excel, dates and time formats are usually retrieved from the windows regional settings. But you can format this and change it to any other location format that you want. To do this, simply go to the Control panel and click Regional and Language. Then click Change the data and time, or number format.

Figure 2: Default date and time formats

Changing date format

There are various ways in which one can display dates in Microsoft Excel. And at the same time, one can change the format of the dates by simply opening the Format cells dialog and then choose from the predefined date formats.

Also, you can select the cells whose dates you want to format, then press Ctrl + 1. This will open the Format Cells dialog. Then switch to Number tab and select Date in the Category list. Then pick you desired date format.

Figure 3: Formatting Excel date

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc