How can I add all the times up individually?

[Mighty Magpie]

Hi,

If any of the following makes any sense, I would be grateful if somone could
offer me some advice as to sorting out the problem

TIA


Time spent might be attributed to more than one task in one day e.g. 2 hours
on task A, 5 hours on Task B etc

I want one cell on 'Sheet B' to look at all the times spent on a certain
task and then for it to add those times together so as to show how much time
was spent on that task

I then want another cell to do the same for another of the tasks etc etc

On SHEET A the following applies

Row J = named Start Time (formatted to 0000)
Row H = named Finish time (formatted to 0000)

Cell AK5 named MAN HOURS =AZ5
Cell AL5 named ACTUAL TIME SPENT (Minutes) =AY5

Cell AQ5 named FROM (hours) =INT(J5/100)
Cell AR5 names FROM (minutes) =J5-(AQ5*100)
Cell AT5 named TO (hours) =INT(K5/100)
Cell AU5 named TO (minutes) =K5-(AT5*100)

Cell AW5 named BASE MINUTES =(AT5*60+AU5)-(AQ5*60+AR5)
Cell AX5 named ACTUAL MINUTES =IF(AW5<=0,AW5+1440,AW5)
Cell AY5 named SINGLE TIME =IF(J5>0,(IF(ISBLANK(K5),5,AX5)),"")
Cell AZ5 named MAN HOURS =IF(ISBLANK(J5),"",AY5*COUNT(E5:F5))

Cell AK3 showing the totals in Hours =INT(SUBTOTAL(9,AK5:AK14942)/60)
Cell AL3 showing the totals in Hours =INT(SUBTOTAL(9,AL5:AL15000)/60)

A39 on Sheet B ='Sheet A'!AK3

It looks awfully complex to me

Cheers

 

[Paul Falla]

Without looking too deeply at the details of what you are
trying to achieve, a pivot table may hold the solution.

Paul Falla

 

[Mighty Magpie]

I want the results to be automatically inputted into the relevant cell(s) on
Sheet B so that the total times spent on each "task" is automatically
entered. As I understand it, the pivot table would 'grow' each time new data
is entered so I would not be able to insert a fomula into the same cell each
time. Is that correct?

 

[Guest]

Hi Mighty Magpie
I really don't know what all of your formulas are trying to accomplish. However, if you have one column with the task description and another with the time for that task, you can use the SUMIF function

=SUMIF(A2:A20,"b",B2:B20

The above example will check column A for the letter b, and if it occurs the value in column B will be included in the sum

Good Luck
Mark Graesse
(e-mail address removed)
Boston MA
----- Mighty Magpie wrote: ----

Hi

If any of the following makes any sense, I would be grateful if somone coul
offer me some advice as to sorting out the proble

TI


Time spent might be attributed to more than one task in one day e.g. 2 hour
on task A, 5 hours on Task B et

I want one cell on 'Sheet B' to look at all the times spent on a certai
task and then for it to add those times together so as to show how much tim
was spent on that tas

I then want another cell to do the same for another of the tasks etc et

On SHEET A the following applie

Row J = named Start Time (formatted to 0000
Row H = named Finish time (formatted to 0000

Cell AK5 named MAN HOURS =AZ
Cell AL5 named ACTUAL TIME SPENT (Minutes) =AY

Cell AQ5 named FROM (hours) =INT(J5/100
Cell AR5 names FROM (minutes) =J5-(AQ5*100
Cell AT5 named TO (hours) =INT(K5/100
Cell AU5 named TO (minutes) =K5-(AT5*100

Cell AW5 named BASE MINUTES =(AT5*60+AU5)-(AQ5*60+AR5
Cell AX5 named ACTUAL MINUTES =IF(AW5<=0,AW5+1440,AW5
Cell AY5 named SINGLE TIME =IF(J5>0,(IF(ISBLANK(K5),5,AX5)),""
Cell AZ5 named MAN HOURS =IF(ISBLANK(J5),"",AY5*COUNT(E5:F5)

Cell AK3 showing the totals in Hours =INT(SUBTOTAL(9,AK5:AK14942)/60
Cell AL3 showing the totals in Hours =INT(SUBTOTAL(9,AL5:AL15000)/60

A39 on Sheet B ='Sheet A'!AK

It looks awfully complex to m

Cheer