I've set up my own timesheet format, where total time spent on a particular
project is added up automatically. Problem is that when I try to add up all
the accumulated time spent over a week (using sum function), it doesn't work.
It adds none of the data together. I've tried using -sum((x:y),"h:mm"), but
that doesn't work either. Any advice appreciated...
Thanks

drewer,

Times are just numbers, with 1 being 24 hours. So 6 hours is .25, etc.
Excel sums them just like any other numbers, so

=SUM(A1:A10)

will provide the sum of the times in cells A1:A10.

However, if the sum is greater than 24 hours, Excel will roll-over the
results greater than 24 unless the cell is formatted for [h]:mm.

To get decimal hours, simply multiply the sum by 24 and format as decimal.

HTH,
Bernie
MS Excel MVP


"drewer" wrote in message
...
I've set up my own timesheet format, where total time spent on a
particular
project is added up automatically. Problem is that when I try to add up
all
the accumulated time spent over a week (using sum function), it doesn't
work.
It adds none of the data together. I've tried using -sum((x:y),"h:mm"),
but
that doesn't work either. Any advice appreciated...
Thanks

Thanks - that doesn't work, I'm afraid. The hours (as hours, not as parts of
a day)appear in the total column, calculated using the TEXT function taking
away the start time from the end time, and totalling them. Where I use sum to
total e.g. Q8:Q43, the value is simply 0:00, despite there being figures
between Q8 and Q43.
Thanks
Andrew

"Bernie Deitrick" wrote:

drewer,

Times are just numbers, with 1 being 24 hours. So 6 hours is .25, etc.
Excel sums them just like any other numbers, so

=SUM(A1:A10)

will provide the sum of the times in cells A1:A10.

However, if the sum is greater than 24 hours, Excel will roll-over the
results greater than 24 unless the cell is formatted for [h]:mm.

To get decimal hours, simply multiply the sum by 24 and format as decimal.

HTH,
Bernie
MS Excel MVP


"drewer" wrote in message
...
I've set up my own timesheet format, where total time spent on a
particular
project is added up automatically. Problem is that when I try to add up
all
the accumulated time spent over a week (using sum function), it doesn't
work.
It adds none of the data together. I've tried using -sum((x:y),"h:mm"),
but
that doesn't work either. Any advice appreciated...
Thanks

drewer,

No need to use the TEXT function - just format the values as time. IF you
must, then for the SUM use the array formula (entered with Ctrl-Shift-Enter)

=SUM(IF(Q8:Q43<"",(Q8:Q43)*1))

HTH,
Bernie
MS Excel MVP


"drewer" wrote in message
...
Thanks - that doesn't work, I'm afraid. The hours (as hours, not as parts
of
a day)appear in the total column, calculated using the TEXT function
taking
away the start time from the end time, and totalling them. Where I use sum
to
total e.g. Q8:Q43, the value is simply 0:00, despite there being figures
between Q8 and Q43.
Thanks
Andrew

"Bernie Deitrick" wrote:

drewer,

Times are just numbers, with 1 being 24 hours. So 6 hours is .25, etc.
Excel sums them just like any other numbers, so

=SUM(A1:A10)

will provide the sum of the times in cells A1:A10.

However, if the sum is greater than 24 hours, Excel will roll-over the
results greater than 24 unless the cell is formatted for [h]:mm.

To get decimal hours, simply multiply the sum by 24 and format as
decimal.

HTH,
Bernie
MS Excel MVP


"drewer" wrote in message
...
I've set up my own timesheet format, where total time spent on a
particular
project is added up automatically. Problem is that when I try to add
up
all
the accumulated time spent over a week (using sum function), it
doesn't
work.
It adds none of the data together. I've tried
using -sum((x:y),"h:mm"),
but
that doesn't work either. Any advice appreciated...
Thanks

=SUMPRODUCT(--(q8:q43))
format your total [h]:mm

Lance

"There are only two kinds of people in the world...
Those that think there are only two kinds of people in the world...
... and those who don't"


"drewer" wrote:

Thanks - that doesn't work, I'm afraid. The hours (as hours, not as parts of
a day)appear in the total column, calculated using the TEXT function taking
away the start time from the end time, and totalling them. Where I use sum to
total e.g. Q8:Q43, the value is simply 0:00, despite there being figures
between Q8 and Q43.
Thanks
Andrew

"Bernie Deitrick" wrote:

drewer,

Times are just numbers, with 1 being 24 hours. So 6 hours is .25, etc.
Excel sums them just like any other numbers, so

=SUM(A1:A10)

will provide the sum of the times in cells A1:A10.

However, if the sum is greater than 24 hours, Excel will roll-over the
results greater than 24 unless the cell is formatted for [h]:mm.

To get decimal hours, simply multiply the sum by 24 and format as decimal.

HTH,
Bernie
MS Excel MVP


"drewer" wrote in message
...
I've set up my own timesheet format, where total time spent on a
particular
project is added up automatically. Problem is that when I try to add up
all
the accumulated time spent over a week (using sum function), it doesn't
work.
It adds none of the data together. I've tried using -sum((x:y),"h:mm"),
but
that doesn't work either. Any advice appreciated...
Thanks

Ok - apparently I'm a monkey, but couldn't use Lance or Bernie's answers and
get them back to work. FOrmula I'm using for calculating each line of
activity (with separate column for each day, and within each day, separate
column for start and end times) is to =TEXT((end time-start time for day
1)+(end time-start time for day 2) and so on),"h:mm"). This gives me a final
total number of hours (in "h:mm" format), but they won't add up. Sumproduct
and sumif don't like they will do what I want them to do - i.e. calculate how
many hours have been worked in total in a week.

Thanks again for your help...
Andrew

"LanceB" wrote:

=SUMPRODUCT(--(q8:q43))
format your total [h]:mm

Lance

"There are only two kinds of people in the world...
Those that think there are only two kinds of people in the world...
... and those who don't"


"drewer" wrote:

Thanks - that doesn't work, I'm afraid. The hours (as hours, not as parts of
a day)appear in the total column, calculated using the TEXT function taking
away the start time from the end time, and totalling them. Where I use sum to
total e.g. Q8:Q43, the value is simply 0:00, despite there being figures
between Q8 and Q43.
Thanks
Andrew

"Bernie Deitrick" wrote:

drewer,

Times are just numbers, with 1 being 24 hours. So 6 hours is .25, etc.
Excel sums them just like any other numbers, so

=SUM(A1:A10)

will provide the sum of the times in cells A1:A10.

However, if the sum is greater than 24 hours, Excel will roll-over the
results greater than 24 unless the cell is formatted for [h]:mm.

To get decimal hours, simply multiply the sum by 24 and format as decimal.

HTH,
Bernie
MS Excel MVP


"drewer" wrote in message
...
I've set up my own timesheet format, where total time spent on a
particular
project is added up automatically. Problem is that when I try to add up
all
the accumulated time spent over a week (using sum function), it doesn't
work.
It adds none of the data together. I've tried using -sum((x:y),"h:mm"),
but
that doesn't work either. Any advice appreciated...
Thanks

You already got the explanation so why do you insist using TEXT? Just use
end_time - start_time and SUM and format as [hh]:mm. However the sumproduct
formula works, you just have to format the result as

[hh]:mm or using your "method"

=TEXT(SUMPRODUCT(--(Q8:Q43)),"[hh]:mm")

but it is totally a waste of space using text unless you don't plan to
calculate the hours which you obviously want to do

--
Regards,

Peo Sjoblom


"drewer" wrote in message
...
Ok - apparently I'm a monkey, but couldn't use Lance or Bernie's answers
and
get them back to work. FOrmula I'm using for calculating each line of
activity (with separate column for each day, and within each day, separate
column for start and end times) is to =TEXT((end time-start time for day
1)+(end time-start time for day 2) and so on),"h:mm"). This gives me a
final
total number of hours (in "h:mm" format), but they won't add up.
Sumproduct
and sumif don't like they will do what I want them to do - i.e. calculate
how
many hours have been worked in total in a week.

Thanks again for your help...
Andrew

"LanceB" wrote:

=SUMPRODUCT(--(q8:q43))
format your total [h]:mm

Lance

"There are only two kinds of people in the world...
Those that think there are only two kinds of people in the world...
... and those who don't"


"drewer" wrote:

Thanks - that doesn't work, I'm afraid. The hours (as hours, not as
parts of
a day)appear in the total column, calculated using the TEXT function
taking
away the start time from the end time, and totalling them. Where I use
sum to
total e.g. Q8:Q43, the value is simply 0:00, despite there being
figures
between Q8 and Q43.
Thanks
Andrew

"Bernie Deitrick" wrote:

drewer,

Times are just numbers, with 1 being 24 hours. So 6 hours is .25,
etc.
Excel sums them just like any other numbers, so

=SUM(A1:A10)

will provide the sum of the times in cells A1:A10.

However, if the sum is greater than 24 hours, Excel will roll-over
the
results greater than 24 unless the cell is formatted for [h]:mm.

To get decimal hours, simply multiply the sum by 24 and format as
decimal.

HTH,
Bernie
MS Excel MVP


"drewer" wrote in message
...
I've set up my own timesheet format, where total time spent on a
particular
project is added up automatically. Problem is that when I try to
add up
all
the accumulated time spent over a week (using sum function), it
doesn't
work.
It adds none of the data together. I've tried
using -sum((x:y),"h:mm"),
but
that doesn't work either. Any advice appreciated...
Thanks