How Can I Calculate the Number of Hours between Two Different Time Formula Values?

Brian McElligott

I have a form that collects times of day, into a sheet via a dropdown List of times (9:00 AM, 9:30 AM, 10:00 AM, ect).

I have converted these values into a time using the TIME function. So now I have 4 columns:

Start Time (dropdown list value from the form)

End Time (dropdown list value from the form)

Start Time 12 Hr (converting start time using the TIME function, of data type Text/Number)

End Time 12 Hr (converting the end time using the TIME function, of data type Text/Number)

When I try to determine the number of hours between the Start Time 12 Hr and the End Time 12 Hr, using both the Time function (=Time([Start Time 12 Hr]@row - [End Time 12 Hr]@row), I am expecting a decimal value. Instead I receive an error - #INVALID OPERATION.

Any thoughts?

Brian Wilson DC

@Brian McElligott It is because you need to reuse the TIME function for your calculation steps. You actually do not even need to create the conversion columns you have and simply reference the drop down menu, as long as you have your AM and PM designations in those timestamps the following should work:

=(TIME([Start Time]@row) - TIME([Finish Time]@row))

If you use that formula for the example above that hour long time should result in the decimal 0.04167, or 1/24th of a day.

If you add the following to that formula you can have it return hour increments, resulting in it returning "1" hour in a day:
=(TIME([Start Time]@row) - TIME([Finish Time]@row))*24

If you add the following to that new formula you can have it return total minutes, resulting in it returning "60" minutes:

=(TIME([Start Time]@row) - TIME([Finish Time]@row))*24*60

I hope this helps!

Brian Wilson DC

@Brian McElligott It is because you need to reuse the TIME function for your calculation steps. You actually do not even need to create the conversion columns you have and simply reference the drop down menu, as long as you have your AM and PM designations in those timestamps the following should work:

=(TIME([Start Time]@row) - TIME([Finish Time]@row))

If you use that formula for the example above that hour long time should result in the decimal 0.04167, or 1/24th of a day.

If you add the following to that formula you can have it return hour increments, resulting in it returning "1" hour in a day:
=(TIME([Start Time]@row) - TIME([Finish Time]@row))*24

If you add the following to that new formula you can have it return total minutes, resulting in it returning "60" minutes:

=(TIME([Start Time]@row) - TIME([Finish Time]@row))*24*60

I hope this helps!