Searched - time formulas (as they relate to group/individual tasks)

[NYdubber]

First off, thanks for this place. I've snooped around here for awhile (ok, years) but now I finally had to register as I got stuck on something and knew this was the only place to come as far as good, quality, answers go.

Here is an example of a spreadsheet I threw together for workers. In a nutshell I need the whole day total and then each task for that day totaled.

I thought I figured out the whole day total, but soon figured out I was in way over my head as far as time SUM formulas were concerned. So instead of beating my head against a wall, here I am. I searched, still couldn't find what I needed.

Thanks again for any help, I appreciate it.

 

[mikerickson]

in A15, you could put the formula =SUMIF(B2:B10, 210, E2:E20) to get the total time spent on task 210
Or (again in A15)you could put the formula

=SUMIF($B$1:$B$10, A14, $E$1:$E$10) and drag right, so the formula can get the task number from row 14 rather than it being hard-coded.

 

[NYdubber]

I had that and even when I had values for the task I would get a "0" or a "00:00" result in A15, regardless of how I formatted the cell.

I tried custom formatting ("[h]:mm") and that only gave me "00:00", as well.


Thanks for the quick response.

 

[mikerickson]

What are the formulas in column E?

 

[NYdubber]

=TEXT(D2-C2, "h:mm")


...so on and so forth. I've messed with it, but still get "00:00" regardless of what I've done with the formula in 'E'.

 

[mikerickson]

The problem is that column E has text rather than true Excel serial time. (one can't SUM text).
You could use the formula (in E2) =D2-C2 and then format the cells with the custom format h:mm. Then the SUMIF will work.

 

[NYdubber]

That did it - all I had to do was reformat 'E' back to the custom setting.

Thanks, I really appreciate it!