I need help with a Google Sheets formula for analyzing video data. My team watches educational videos and tracks how often certain behaviors happen during specific time segments.
Here’s what I’m trying to do: I want to enter start and end timestamps like 15:30 to 17:45 (meaning 15 minutes 30 seconds to 17 minutes 45 seconds into the video, not actual clock times). Then I count how many times something occurred during that period and want to calculate the frequency per minute.
Example scenario:
- Start timestamp: 15:30
- End timestamp: 17:45
- Event count: 6
- Desired result: 6 events ÷ 2.25 minutes = 2.67 events per minute
The problem is Google Sheets interprets my mm:ss format as actual time values instead of duration markers. When I try =6/(17:45-15:30) it returns weird results instead of the rate calculation I need.
I think I need a way to convert these timestamp differences into decimal minutes first, then divide my event count by that duration. Has anyone solved something similar? Any formula suggestions would be really helpful!
there’s actually a simpler way - just use MINUTE and SECOND functions directly. try =6/((MINUTE(17:45)+SECOND(17:45)/60)-(MINUTE(15:30)+SECOND(15:30)/60)). this converts timestamps to decimal minutes without complex math. works great for behavior coding sheets and way easier to remember than the 1440 trick.
I ran into this same issue when coding research videos. Just multiply your timestamps by 1440 to get decimal minutes. Google Sheets treats time as fractions of a day, so 1440 (minutes per day) converts it to actual minutes. Your formula becomes =6/((17:45-15:30)*1440) and you’ll get 2.67 like you want. No need for separate cells or messy hour/minute extractions - the multiplication does it all. I’ve used this on hundreds of video segments and it’s always accurate. Just make sure your timestamp cells are formatted as time values.
I faced a similar issue when analyzing presentation metrics. The key is to recognize that Google Sheets interprets mm:ss as time values. To work around this, first convert the timestamps to seconds. You can do this using the formula =HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1) for each timestamp. After obtaining the duration in seconds, you can calculate the minute rate. For your specific case, use this final formula: =6/((HOUR(B1)*3600+MINUTE(B1)*60+SECOND(B1))-(HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)))/60) where A1 represents the start time and B1 the end time. While it seems complex, this approach will yield the correct decimal minutes needed for your calculation.