add hours (calculated by functions) on a timesheet

[drewer]

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

 

[Bernie Deitrick]

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]

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

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

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

 

[Bernie Deitrick]

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

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

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

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

 

[LanceB]

=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"

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

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

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]

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

=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"

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

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


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

 

[Peo Sjoblom]

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

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

=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"

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

:

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


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