A desk log says a room was booked at 14:30 and released at 16:00. Both entries arrived as text. Convert them alongside the original column so you can calculate how long the room was occupied and still see what came from the log.

Keep the source column visible

Put 14:30 and 16:00 in A2 and A3 as text. If typing the example by hand, a leading apostrophe tells the spreadsheet to keep the value as text. Enter the following in B2 and fill down to B3:

=TIMEVALUE(A2)

You might see about 0.60417 for 14:30. One whole day is 1, and 14½ hours is a little more than 0.6 of a day. Apply a time format to B2:B3 to display the clock readings again.

TIMEVALUE converts text 14:30 to about 0.60417 of a day, displayed as 14:30 after applying a time format.

Illustrated conversion. The time format controls how the numeric value is displayed.

Subtract two readings from the same date

=(B3-B2)*1440

Format the result cell as a regular number. It should show 90, because a day contains 1,440 minutes. If the import contains both late-night and early-morning entries, keep the date attached to each timestamp so a shift that crosses midnight can be calculated against the right day.

The later time of 16:00 minus 14:30 yields a 90-minute interval.

Illustrated result check. Display the difference formula as a number.

Microsoft’s TIMEVALUE reference