I am sure that every excel user like you 😉 frequently work with dates and times in excel. Some may follow the excel date format in DD-MM-YYYY (like in India) and others may follow MM-DD-YYYY (like in US). Similarly, the time may be in HH:MM or HH:MM:SS format in excel.
But do you know how excel understands and interprets the dates and times in the backend? Probably your answer is NO!
No worries 😎 In this tutorial, we would learn everything about what excel does behind the scenes while you enter any date and time in the excel cell.
Here we go 😎
Basics of Date and Time Format in Excel
The default excel date and time format are always the computer system’s format.
You can access the default formats by selecting the ‘Short Date’, ‘Long Date’, ‘Time’ options under ‘Home’ tab, as shown below:
The above formats (DD-MM-YYYY, DD MMMM YYYY, and HH:MM:SS) correspond to the computer system’s regional formats under the control panel.
Consequently, if you change the computer system’s date/time formats, it would automatically update the excel default formats.
Let us learn how excel reads and interprets the date or time that you enter the cell.
Behind the Scenes 😉 – Excel Date
As soon as you enter any date (any format) in an excel cell, the excel stores that date as a numerical number.
This means that the date in the DD-MM-YYYY or DD MMM YY or any other such format is simply visible to you. Behind the scenes, Excel understands and stores it in the form of some numerical value. But, what is this numerical value of date? Read below text.
To understand this, let us do a very simple exercise. Open a new excel workbook and type the date 01-01-1900 in any of the cells and press Enter. Now, select this cell and change the cell format to ‘General’.
As a result, you would notice that the excel returns 1 as output.
Do you know why did this happen? The simple answer to this is that for excel, the date 01-01-1900 is 1.
Now, change the date to 02-01-1900 and cell format to ‘General’. The result is 2. It means excel interprets date 02-01-1900 as 2.
Likewise, as you move far from the date 01-01-1900, the numerical value increase by 1.
Therefore, the numerical value of the date 19-10-2021 is 44488. This simply denotes, the date 19-10-1996 is 44488 days away from 01-01-1900.
Behind the Scenes 😉 – Excel Time
Similar to the date, excel stores and interprets the time as a decimal value. Herein, 0.00 represents 12 AM (00:00:00) and 0.99999 represents 23:59:59.
All the other times lies between these two decimal values. For example, 0.208333 represents 05:00:00 AM and 0.50000 denotes 12:00:00 PM.
2 Methods to Find Number Value of Date and Time
There are two methods for finding the number value of date and time in excel:
- Changing Cell Format to ‘General’ (without formula)
- Using DATEVALUE and TIMEVALUE excel formula
Method 1 # Change Cell Format to General
If you have a date in a cell and you want to find the numerical value of that date without using formula, follow these steps:
- Select cell(s) containing date (say date is 01-04-2020)
- Navigate to the ‘Home’ tab > ‘Number’ group. Click on the drop down button and select the format as ‘General’
As a result, excel returns the number value of the date as 43922.
Likewise, it works the same in the case of time.
Method 2 # Using Excel DATEVALUE and TIMEVALUE Excel Function
Excel has come up with a separate formula to convert the date or time into the corresponding date and time number format.
Simply pass the date within double-quotes as function argument.
As a result, excel returns the number value, as shown below:
The same goes with the TIMEVALUE function:
Thank you for reading 🙂