< Go Back

Extract time from a date and time

What “Extract time from a date and time” means.

Extracting time from a date and time is just a regular method use by excel in identifying a time and delivering it to you!

EXTRACTING TIME FROM DATE AND TIME.

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)

= 0.57292125

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

MOD(5,2)= 1

It can be used in extracting time from date and time due to the fact that the serial number and fraction (Date and Time) takes 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

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar