Saturday, August 30, 2014

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

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

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?

Thank you very much in advance for your time!


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:

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!!