The Sum Of Time

In my table I have a column of timings which I want to
total in hours, minutes & seconds.

Does anyone know how I can do this using a formula or I'm
I going about this the wrong way? Does the timings in the
column need to be formatted in a special way so that WORD
recognize them as timings?

Thanks for you time in reading this query.

It would be MUCH easier to put the timing data into Excel and do the
math there. You can link the Excel spreadsheet to a Word document, or
even embed the Excel spreadsheet into a Word document.


The Taz Man wrote:

In my table I have a column of timings which I want to
total in hours, minutes & seconds.

Does anyone know how I can do this using a formula or I'm
I going about this the wrong way? Does the timings in the
column need to be formatted in a special way so that WORD
recognize them as timings?

Thanks for you time in reading this query.

I've been able to do this in Excel, but I think it would be tricky in Word;
probably easier to embed an Excel sheet. In any case, you'd need separate
columns for hours, minutes, and seconds (or at least I couldn't figure any
way around that in Excel).

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"The Taz Man" wrote in message
...
In my table I have a column of timings which I want to
total in hours, minutes & seconds.

Does anyone know how I can do this using a formula or I'm
I going about this the wrong way? Does the timings in the
column need to be formatted in a special way so that WORD
recognize them as timings?

Thanks for you time in reading this query.

Agree that you need to use Excel, but you don't need to separate the hours,
minutes, and seconds into separate columns. Date/time values in Excel (and
in VBA) are doubles in drag. The integer part is the date, the decimal part
is the time. Excel's time formats -- eg hh:mm:ss -- are just ways to
*display* a decimal value. Internally, those values are still decimals, and
can be summed as such.




"Suzanne S. Barnhill" wrote in message
...
I've been able to do this in Excel, but I think it would be tricky in
Word;
probably easier to embed an Excel sheet. In any case, you'd need separate
columns for hours, minutes, and seconds (or at least I couldn't figure any
way around that in Excel).

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup
so
all may benefit.

"The Taz Man" wrote in message
...
In my table I have a column of timings which I want to
total in hours, minutes & seconds.

Does anyone know how I can do this using a formula or I'm
I going about this the wrong way? Does the timings in the
column need to be formatted in a special way so that WORD
recognize them as timings?

Thanks for you time in reading this query.

I'd like to know more about that. I keep logs to keep track of hours worked,
and it seems to me that I tried just adding times and found that I could add
times to times, but they reset after 24 hours; that is, they gave clock
times and not cumulative hours. As a result I worked out a complex formula
that converted minutes to hours and shifted the hours to the hours column,
leaving the excess minutes in the minutes column.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"Jezebel" wrote in message
...
Agree that you need to use Excel, but you don't need to separate the
hours,
minutes, and seconds into separate columns. Date/time values in Excel (and
in VBA) are doubles in drag. The integer part is the date, the decimal
part
is the time. Excel's time formats -- eg hh:mm:ss -- are just ways to
*display* a decimal value. Internally, those values are still decimals,
and
can be summed as such.




"Suzanne S. Barnhill" wrote in message
...
I've been able to do this in Excel, but I think it would be tricky in
Word;
probably easier to embed an Excel sheet. In any case, you'd need
separate
columns for hours, minutes, and seconds (or at least I couldn't figure
any
way around that in Excel).

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup
so
all may benefit.

"The Taz Man" wrote in message
...
In my table I have a column of timings which I want to
total in hours, minutes & seconds.

Does anyone know how I can do this using a formula or I'm
I going about this the wrong way? Does the timings in the
column need to be formatted in a special way so that WORD
recognize them as timings?

Thanks for you time in reading this query.

G'Day Suzanne,

If you us the format "h:mm:ss" the sum will rollover (reset)
by whole days, however if you use "[h]:mm:ss" it will
correctly accumulate hours - useful for all logs and
timesheets etc.

The point Jezebel makes about internal time format, goes
largely unrecognized and even when recognized, is often
not fully understood or leveraged.

As she points out, it is a decimal of the form:
..........days.dayfraction..........

Try the following:
Format some cells as hh:mm:ss, and try entering a
decimal value:
0.75 18:00:00
0.5 12:00:00
0.25 6:00:00
0.1 2:24:00 (2.4 Hours)
0.05 1:12:00 (1.2 Hours) etc.

Also if you have a SUM() of times, and you would like
it expressed in decimal time, format THAT cell as
NUMBER:
SUM() will give decimal days
SUM()*24 will give decimal hours etc etc

You can then apply ROUND, INT etc to taste.

This applies to ANY function that returns a date/time
value.

There are a couple of things to watch for:
Any arbitrary DATE, will have a time format:
..........xxx.0............
since no time value is entered. Effectivly Date refers
to midnight on the Day Before. If you calculate
Date - TODAY, the result will have the form:
.......DaysDifference.TimeTodayWhenCalculated..... ....

For Excel ....xxx is the number of days since 1st Jan 1900.

(You can select 1904 in ToolsOptions - this has been the
cause of many a panic when, during holiday time, "friends"
have altered the setting!)

I do hope this helps, and has not been overlong.
--
Regards,
Pat Garard
Australia

______________________________________

G'Day Taz Man,

Word will not do what you require. Can you live with that?

If not, look at http://www.openoffice.org/, and you will see
a FREE download available of OpenOffice.org v1.1.1 - a full
featured office suite comprising:
Writer - Word Processor
Calc - Spreadsheet
Impress - Presentation and
Draw - Drawing.

These products are directly related to Sun StarOffice, have a
surprising degree of compatibility with MS Office, and do
seem to be stable.

Writer DOES have the facility you require. Table calculations
are considerably better featured than in Word.

Note however that once you employ this feature in Writer, you
will not be able to transfer/convert to Word.

(If you stick to a feature-set that is common to Word and Writer,
then you can open and save .doc format files in either program.)

I hope this helps.
--
Regards,
Pat Garard (The Oz Man)
Australia

______________________________________

Hello Everyone!

Thank you all for your time in replying to my query. I am
going to give Pats suggested OpenOffice program a whirl
but also try to get to grips with Exel. At least I now
know it can't be done with WORD which will stop me from
pulling my hair out!

Once again, thanks all.

Regards,
Jason.
(England)

-----Original Message-----
G'Day Suzanne,

If you us the format "h:mm:ss" the sum will rollover
(reset)
by whole days, however if you use "[h]:mm:ss" it will
correctly accumulate hours - useful for all logs and
timesheets etc.

The point Jezebel makes about internal time format, goes
largely unrecognized and even when recognized, is often
not fully understood or leveraged.

As she points out, it is a decimal of the form:
..........days.dayfraction..........

Try the following:
Format some cells as hh:mm:ss, and try entering a
decimal value:
0.75 18:00:00
0.5 12:00:00
0.25 6:00:00
0.1 2:24:00 (2.4 Hours)
0.05 1:12:00 (1.2 Hours) etc.

Also if you have a SUM() of times, and you would like
it expressed in decimal time, format THAT cell as
NUMBER:
SUM() will give decimal days
SUM()*24 will give decimal hours etc etc

You can then apply ROUND, INT etc to taste.

This applies to ANY function that returns a date/time
value.

There are a couple of things to watch for:
Any arbitrary DATE, will have a time format:
..........xxx.0............
since no time value is entered. Effectivly Date refers
to midnight on the Day Before. If you calculate
Date - TODAY, the result will have the form:
.......DaysDifference.TimeTodayWhenCalculated.....
.....

For Excel ....xxx is the number of days since 1st Jan
1900.

(You can select 1904 in ToolsOptions - this has been the
cause of many a panic when, during holiday
time, "friends"
have altered the setting!)

I do hope this helps, and has not been overlong.
--
Regards,
Pat Garard
Australia

______________________________________


.

Thanks, Pat. I'll give that a try next time I set up a log of this nature. I
also need to be able to multiply the hours and minutes by an hourly rate,
which I'm currently also doing using a sum of the rate times the hours and
minutes separately.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
Word MVP FAQ site: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

"Pat Garard" apgarardATbigpondPERIODnetPERIODau wrote in message
...
G'Day Suzanne,

If you us the format "h:mm:ss" the sum will rollover (reset)
by whole days, however if you use "[h]:mm:ss" it will
correctly accumulate hours - useful for all logs and
timesheets etc.

The point Jezebel makes about internal time format, goes
largely unrecognized and even when recognized, is often
not fully understood or leveraged.

As she points out, it is a decimal of the form:
..........days.dayfraction..........

Try the following:
Format some cells as hh:mm:ss, and try entering a
decimal value:
0.75 18:00:00
0.5 12:00:00
0.25 6:00:00
0.1 2:24:00 (2.4 Hours)
0.05 1:12:00 (1.2 Hours) etc.

Also if you have a SUM() of times, and you would like
it expressed in decimal time, format THAT cell as
NUMBER:
SUM() will give decimal days
SUM()*24 will give decimal hours etc etc

You can then apply ROUND, INT etc to taste.

This applies to ANY function that returns a date/time
value.

There are a couple of things to watch for:
Any arbitrary DATE, will have a time format:
..........xxx.0............
since no time value is entered. Effectivly Date refers
to midnight on the Day Before. If you calculate
Date - TODAY, the result will have the form:
.......DaysDifference.TimeTodayWhenCalculated..... ....

For Excel ....xxx is the number of days since 1st Jan 1900.

(You can select 1904 in ToolsOptions - this has been the
cause of many a panic when, during holiday time, "friends"
have altered the setting!)

I do hope this helps, and has not been overlong.
--
Regards,
Pat Garard
Australia

______________________________________

Ok Suzanne,

Bear in mind that the basic time integer is Days.

When you've set it up, you'll use the =SUM(...) to give you the
Total Hours in "[h]:mm" format in (say) cell J20.

To calculate the Bill, use the SAME SUM(...):
= J20*24(hours)*rate($/hour) (= $Chargable)

You can round J20 to the nearest 15min (say) using
=INT(SUM(...) * 24 * 4 +0.5) / 4 / 24
| | | | | |
Days | QtrHrs | Hrs |
Hrs NrstQtr Days (again)
to the nearest
15min

(all the while DISPLAYING as [h]:mm).

Alternatively format J20 as a Number (2 dec places), enter
"decimal minutes." into K20, and (in J12) use:
=INT(SUM(...) * 24 * 4 +0.5) / 4
| | | | |
Days | QtrHrs | Hrs (to the nearest 0.25 hr)
Hrs NrstQtr


All the best now - Have Fun!!
--
Regards,
Pat Garard
Australia

______________________________________