Spreadsheet Time Calculation Problems

Other Questions Google docs
1

Hey everyone! I’m having some trouble with my spreadsheet. It’s got columns for start time, end time, and duration. The duration is calculated by subtracting start from end. But when I try to add up all the durations, I keep getting weird negative numbers. What’s going on?

Also, I want to filter the data by date and add up durations for specific time periods. Like, how can I get the total duration for just the last month? Any tips would be super helpful!

Here’s a sample of what my data looks like:

Begin    Finish   Length
09:30    13:45    04:15
14:20    16:10    01:50
18:00    22:45    04:45
23:15    03:20    04:05

Thanks in advance for any advice!

2

sounds like ur dealing with time wraparound, mate. for negatives, try using a formula that handles 24-hour cycles. as for filtering by date, u could add a date column and use SUMIFS() with date criteria. hope that helps!

3

I’ve run into this exact problem before! The negative numbers are definitely from times crossing midnight. What worked for me was using a custom formula: =IF(B2<A2,B2+1,B2)-A2 where A2 is start time and B2 is end time. This adds a day when the end time is earlier than the start time.

For filtering by date, I’d suggest adding a date column next to your times. Then you can use pivot tables to sum durations for specific periods. Just drag the date field to the ‘Rows’ area and duration to the ‘Values’ area in the pivot table builder. It’s super easy to adjust date ranges this way too.

Hope this helps solve your spreadsheet woes!

4

The negative numbers issue likely stems from time wrapping around midnight. To fix this, you can use the MOD function in your duration calculation: =MOD(end_time - start_time, 1). This ensures correct results even when crossing midnight.

For date-based filtering and summing, add a date column to your sheet. Then use SUMIFS with date criteria to total durations for specific periods. For example, to sum last month’s durations:

=SUMIFS(duration_range, date_range, “>=”&EOMONTH(TODAY(),-2)+1, date_range, “<=”&EOMONTH(TODAY(),-1))

This formula assumes your date column uses actual dates, not text. Adjust column references as needed for your specific spreadsheet layout.