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