Calculating total daily time in a spreadsheet with time ranges per cell

user_72321 0
Viewed 119

I have a basic spreadsheet to keep track of time spent on an activity.

The idea is to capture a time range per cell. E.g. 10:00 - 12:30 means 2 hours and 30 minutes.

Breaks can be taken during the day. On resuming, a new time range is entered in a new cell.

I want to calculate the total time per day. E.g. Mon Jun 15 is 2.5 + 4.5 = 7 hours.

spreadsheet with time ranges

The algorithm I'm thinking of is more or less

for each cell that contains time ranges for the given day
  start, end = split(cell, " - ")
  diff_decimal = (end - start) * 24
  total += diff_decimal

But I'm not sure how to do this with spreadsheet functions. The starting point I have is using =SPLIT(B2," - ") but I'm already blocked since I'm not sure how to handle the return value.

P.S. The problem could be simplified by having multiple "start" and "end" columns with one value per cell. But I want to try the given format, which I prefer, before trying another approach.

google-sheets time
1 Answers

This is Google Sheets solution, as Excel does not have regex formulas.

Try this formula (place it in G1, column G:G formatted as number):

={
  "Total";
  ARRAYFORMULA(
    IF(
      A2:A = "",
        "",
        MMULT(
          IFERROR(
            (  TIMEVALUE(REGEXEXTRACT(B2:F, "-\s+(\S+)"))
             - TIMEVALUE(REGEXEXTRACT(B2:F, "(\S+)\s+-"))) * 24,
            0
          ),
          SEQUENCE(COLUMNS(B2:F), 1, 1, 0)
        )  
    )
  )
}

If you format G:G as Duration then you can remove * 24 part.

enter image description here

UPD

Added ignoring of the wrong formatted cells with IFERROR - just 0 in this case.

Better add this custom conditional formatting rule for B2:F to mark (in red on the screenshot) time periods in the wrong format and go fix them:

=AND(B2 <> "", NOT(IFERROR(REGEXMATCH(B2, "\b(\d|[01]\d|2[0-3]):[0-5]\d\s*-\s*(\d|[01]\d|2[0-3]):[0-5]\d\b"), False)))
user_2798060
Related