Google Sheets Formula for Time Calculation

Formula Overview

The objective is to calculate the time difference in hours between two time points provided in a text format such as "7am - 4pm", "6:30am - 2pm", or "6:45pm - 3:15pm". The formula needs to extract the starting and ending times, convert them into a numeric time format, and then compute the difference.

Formula Construction

The following formula can be used in Google Sheets to achieve this:

=HOUR(TIMEVALUE(TRIM(MID(A1, FIND("-", A1) + 1, LEN(A1))) )) - HOUR(TIMEVALUE(TRIM(MID(A1, 1, FIND("-", A1) - 1))) ))

Explanation of the Formula Components

1. FIND("-", A1): This function locates the position of the hyphen ("-") in the string in cell A1, which separates the two time points.

2. MID Function:

   • MID(A1, 1, FIND("-", A1) - 1): Extracts the starting time from the string.
   • MID(A1, FIND("-", A1) + 1, LEN(A1)): Extracts the ending time from the string.

3. TRIM Function: This removes any leading or trailing spaces from the extracted time strings.

4. TIMEVALUE Function: Converts the trimmed time strings into Google Sheets time format (as a fraction of a day).

5. HOUR Function: Extracts the hour component from the resulting time.

6. Final Calculation: The difference between the end time and the start time is taken to compute the total hours.

Practical Example

Assuming the following time descriptions are entered in cell A1:

• Input: "7am - 4pm"

  • Formula: =HOUR(TIMEVALUE(TRIM(MID(A1, FIND("-", A1) + 1, LEN(A1))) )) - HOUR(TIMEVALUE(TRIM(MID(A1, 1, FIND("-", A1) - 1))) ))
  • Output: 9 (indicating there are 9 hours between 7am and 4pm)

• Input: "6:30am - 2pm"

  • Formula: Same as above.
  • Output: 7.5 (indicating a 7.5-hour difference)

• Input: "6:45pm - 3:15pm"

  • Formula: Same as above.
  • Output: -3.5 (indicating a negative value, since the end time is earlier than the start time)

Conclusion

This formula effectively calculates the hour difference between two time points formatted as specified. Ensure that the time strings are correctly formatted, and be mindful of the AM/PM distinction to maintain accurate calculations. For a deeper understanding, consider exploring resources available on the Enterprise DNA Platform for advanced topics and more complex time calculations in Google Sheets.