## Saturday, August 30, 2014

### yogi_Formula In Cell C3 For Sum of Hours Worked For Range Of WorkHour Entries Per Spec

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-30-2014
post by Jeremy Falletta:
Can I use an ArrayFormula to perform two functions on one range in a single formula?
Hello,

I'm still learning Sheets, and I'm wondering about how to do something. I have a range on which I want to A) perform math and B) sum the results of A - I'm wondering if this is possible with something like ArrayFormula.

Specifically, I have two columns, both formatted for time: the first is a start time and the second is an end time. What I want to do is calculate the duration between each pair, and then sum those. Currently, my workaround is to have a separate, hidden column that performs the first calculation, and then sum that column. Effective enough, but not very elegant.

Can anyone help?

Cheers,
Jeremy
---
Yogi,

Thank you for the reply, and I'm sorry it took me so long to follow up - been doing lots of experimenting in the mean time!

Anyway, check out this small example I put together, if you have a chance: https://docs.google.com/spreadsheets/d/1gZPQ1QCWWIWMrXAh56a6ES5MkDtrnv8Fwz5ufz6I3Lw/edit?usp=sharing

What I'm actually working on is an employee schedule. Right now, I have two columns (repeated for each day of the week, but that's not important at the moment), one for shift start times and one for shift end times. What I need to do is calculate the total amount of hours I've scheduled on a given day.

Currently, my workaround is to have a separate column (actually hidden in the actual spreadsheet, to prevent clutter) that calculates the duration of the shift, controlling for a lunch break if they are working 8 hours. You'll see that in the formula.

Then I simply sum that column to get a total. But what I want to do is eliminate that worker column, and do the entire calculation in one cell. I'm positive that's possible, but I'm not quite at the point where I can see how to manage it.

Can you assist? Any help is very much appreciated!!

Thanks,
Jeremy
---------------------------------------------------------------------------------------------------------------------------