When you have a series of data in Excel, it might not always be in the form you need. If you have cells that include both the date and time, but you just need to extract the time, this article will show you how to accomplish this task.
EXTRACTING TIME FROM DATE AND TIME.
Extracting time from a date and time is just a regular method use by excel in identifying a time and delivering it to you!
Excel handles Date and Time as serial number and fractions respectively. The serial number is actually gotten from the number of days that have elapsed since the year 1/1/1900. The Time remains a fraction, Excel represents 1hr as 1/24 which is equal to 0.041667 and 1 minute as 0.041667* 1/60 which is equal to 0.00069445. So to calculate the Time 13:45 we will have:
(13 * 0.041667) + (45 * 0.00069445)
To Extract the Time from Date and Time
- Fill the Date and Time column with various dates and time
- Create a second column with its own heading e.g Time value
- Left click the cell adjacent the date and time (the first cell under Time value)
- Type in the formula “=VALUE(A4)” in the formula box and press enter.
Figure 1a. Extracting the cell for serial numbers and fraction from Date and Time
- The Time value of the first date and time will appear.
- Left click and drag on the first result, this will give the rest of the result on that Column.
Figure 1b. Extracting the column of serial numbers and fraction from Date and Time.
* using DATEVALUE will only return the serial numbers which is the Date, it would leave out the fraction which is the Time.
* #VALUE!error means you need to check your Date and time settings.
EXTRACTING TIME FROM DATE AND TIME USING MOD
The MOD (modulus) function actually returns the remainder of the division of two numbers
It can be used in extracting time from date and time because the serial number and fraction (Date and Time) take after the looks of an integer and a fraction!
Therefore the MOD function will always do the job of extracting time from date and time.
Figure 2. Extracting the fraction from the Date and Time
Extracting time from date and time using MOD function is a General format used by Excel in its calculation.
To convert the General format to Date and Time, the function =Time (hour(E4), minute(E4), second(E4)) Is used.
Figure 3a. Extracting a cell of Time from Fractions.
Figure 3b. Extracting a column of Time from Fractions
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.