Having dates associated with times in Excel can sometimes throw off time calculations. Luckily, there is an easy way to strip out date data from any time.
Contents:
1. Removing Date from Time
2. Hiding Date from Time Using Formatting
How to Remove Date from a Time Using a Formula
We can turn the left table into the right table by applying the INT function to each date using the following formula:
= date - INT(date)= A2 - INT(A2)
Because Excel stores dates as a whole number and times as the fractional part of a day (the decimal), we can use the INT function to extract the decimal from each number.
For example, the datetime value 3/16/2023 10:30:00 AM can be represented as the number 45,001.4375, where 45001 represents the number of days that have elapsed since January 1, 1900, and 0.4375 represents 10:30:00 AM (i.e., 43,750 seconds out of 86,400 seconds in a day).
When you use the INT function on a datetime value, it returns only the integer portion of the number. When we subtract the integer value from a number from the same number with a decimal, the two cancel each other out and we are left with just the decimal portion.
This can be easily seen when we apply a general formatting to the same dates. Before we use our formula, the numbers on the left have an integer and decimal portion. After using the formula, the everything to the left of the decimal is removed, and we are left with the decimal (the time portion).
How to Hide Date from a Time Using Formatting
If you would rather keep the date data in each time, but just hide it from view instead, this can easily be accomplished by changing the formatting.
Simply select the times with the formatting you wish to change and select the pop-out button under Home > Number.
This will open up the Format Cells Menu.
Once the menu pops up, select the time format that you wish to use from the available type list. Selecting one without any dates displayed will remove the date portion of your time from view.
