How to Extract Time from DateTime in Google Sheets

This guide will explain how to extract the time component from datetime values in Google Sheets.

Google Sheets allows you to store numerical data as a timestamp containing both a date and a time. However, there may be calculations which only needs to use the time component of the timestamp.

For example, given the timestamp “7/15/2024 7:30:00 AM”, you may just want to use the time “7:30:00 AM” to determine if a particular event occurred during the morning, afternoon, or evening.

To extract time from a datetime value, we can use the MOD functions to retrieve the decimal part of the value, which corresponds to the time component. We can also use the TEXT and TIMEVALUE functions to extract time by converting the timestamp to another format.

In this guide, we will provide a step-by-step tutorial on each method mentioned above and look into concrete use cases for extracting time.

The Anatomy of the TIMEVALUE Function

The syntax of the TIMEVALUE function is as follows:

=TIMEVALUE(time_string)

Let’s look at each argument to understand how to use the TIMEVALUE function.

• = the equal sign is how we start any function in Google Sheets.
• TIMEVALUE() refers to our built-in TIMEVALUE function. This function allows us to return the fraction of a 24-hour day that the time represents. The function returns a number between 0 and 1 where 0 corresponds to 12:00 AM.
• The time_string argument should be a valid string that represents a specific time. Examples of valid time strings include “4:15 AM”, “11:22:59”, and “10:30:45.00 PM”
• Do note that the string must be withing quotations and represents time using either the 12-hour or 24-hour format.
• Dates are ignored in the time_string value.

The Anatomy of the MOD Function

The syntax of the MOD function is as follows:

=MOD(dividend, divisor)

Let’s look at each argument to understand how to use the MOD function.

• = the equal sign is how we start any function in Google Sheets.
• MOD() refers to our built-in MOD function. This function returns the remainder after dividing the given dividend by the given divisor.
• The dividend argument refers to the number to be divided.
• The divisor argument refers to the number to divide by.
• We can use the MOD function to divide our datetime value by 1. The remainder of this operation should be a decimal representing our time.

A Real Example of Extracting Time from DateTime Values in Google Sheets

Using MOD Function

Let’s explore a simple example where we can use the MOD function to extract time from a DateTime value.

In the table above, we have a valid datetime value in cell A2. We would like to return the time portion of this value in cell B2.

To do this, we can use the following formula:

=MOD(A2,1)

The MOD function will return the remainder of the datetime value when divided by 1. The remainder result should be a decimal value that represents the time portion of the timestamp.

In our example above, we can see that our MOD function has successfully extracted the time value from the timestamp.

Using TEXT and TIMEVALUE

We can use the TEXT function with the TIMEVALUE function to extract the time value from a timestamp with both the date and time.

Using the same example earlier, we can use the following formula:

=TIMEVALUE(TEXT(A2,"HH:MM:SS"))

The formula above converts the timestamp in cell A2 into the time format “HH:MM:SS”. The output is then used as an argument for the TIMEVALUE function to create a valid numerical time value.

In the example above, we were able to extract the time value from our timestamp in cell A2. Note that cell B2 must have the time number formatting to view the result as a time instead of a decimal.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to begin extracting time from timestamps in Google Sheets.

How to Extract Time from DateTime in Google Sheets

1. First, select an empty cell where you wish to output the extracted time.
   
   In this example, we’ll enter our formula in cell B2.
2. Let’s try using the MOD function first to extract the time.
   Type the MOD function and enter the cell containing the timestamp as the first argument.
   
   Next, enter 1 as the second argument. This indicates that we want to return the remainder of the datevalue divided by 1.
3. Since the result of the MOD function is a decimal, we must add number formatting to the cell containing our output.
   
   In the toolbar, click on the More Formats button and select Time.
4. After adding the appropriate formatting, we should now have a valid time as our result.
   
5. Another way to extract time from a timestamp is by converting to time format with the TEXT function.
   
6. After converting to a time format, we can use the TIMEVALUE function to return a numerical time value that you can perform calculations on.
   
   

FAQs

1. Is there any formatting needed after extracting the time using MOD?
   Yes, after extracting the time using MOD, you may need to format the cell as Time to display it correctly. Right-click the cell, choose “Format cells”, then select “Time”.
2. Would these formulas work with different datetime formats?
   The methods mentioned in this guide should still work as long as the datetime value is recognized as a valid datetime by Google Sheets. The TIMEVALUE function can also handle various time formats if provided as text.

To learn more about converting date and time values in Google Sheets, you can read our post on how to convert a given number into a date in Google Sheets.

That’s all for this guide! Don’t forget to check out our library of spreadsheet resources, tips, and tricks for both Google Sheets and Microsoft Excel!