sum selected time differences

D

david_g

My employer uses MS-Outlook to book PCs in the training room. I have
to submit weekly usage reports for each of the PCs.
I can export from Outlook to Excel and although the start and end times
look like text '08:00 they subtract OK. There could be several short
bookings for each day. The subject field is used to record the PC
number.
How can I calculate the daily and weekly usage totals for each of PC1,
PC2...PC10 ?
 
B

Bob Phillips

=SUMIF(A:A,"PC1",D:D)

Just make sure that you format the results cell as [h]:mm.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
D

david_g

Thanks Bob but this will sum the all end times (column D) and not the
usage or the difference between the end time (D) and start time (C).
Is it possible to do this directly without inserting a new column and
doing the interim calculation?

The date is in column B and the second part of my question was to sum
the time differences (usage) for each week where a week can be defined
as beginning on a monday so there are usually 4 mondays per month

Bob said:
=SUMIF(A:A,"PC1",D:D)

Just make sure that you format the results cell as [h]:mm.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


david_g said:
My employer uses MS-Outlook to book PCs in the training room. I have
to submit weekly usage reports for each of the PCs.
I can export from Outlook to Excel and although the start and end times
look like text '08:00 they subtract OK. There could be several short
bookings for each day. The subject field is used to record the PC
number.
How can I calculate the daily and weekly usage totals for each of PC1,
PC2...PC10 ?
Click to expand...
Click to expand...
 
B

Bob Phillips

When you said they subtract okay, I assumed you were using a helper column.

It is quite simple to sum them directly

=SUMPRODUCT(--D2:D100-C2:C100)

If you want to limit to dates, then assuming the start of the week date is
F1, then use

=SUMPRODUCT(--(B2:B100>=F1),--(B2:B100<F1+7),--D2:D100-C2:C100)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


david_g said:
Thanks Bob but this will sum the all end times (column D) and not the
usage or the difference between the end time (D) and start time (C).
Is it possible to do this directly without inserting a new column and
doing the interim calculation?

The date is in column B and the second part of my question was to sum
the time differences (usage) for each week where a week can be defined
as beginning on a monday so there are usually 4 mondays per month

Bob said:
=SUMIF(A:A,"PC1",D:D)

Just make sure that you format the results cell as [h]:mm.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


david_g said:
My employer uses MS-Outlook to book PCs in the training room. I have
to submit weekly usage reports for each of the PCs.
I can export from Outlook to Excel and although the start and end times
look like text '08:00 they subtract OK. There could be several short
bookings for each day. The subject field is used to record the PC
number.
How can I calculate the daily and weekly usage totals for each of PC1,
PC2...PC10 ?
Click to expand...
Click to expand...
Click to expand...
 
Top