The Sum Of Time

[The Taz Man]

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.

 

[garfield-n-odie]

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.

 

[Suzanne S. Barnhill]

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).

 

[Jezebel]

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]

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.

 

[Pat Garard]

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 Tools>Options - 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

______________________________________

 

[Pat Garard]

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

______________________________________

 

[The Taz Man]

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)

 

[Suzanne S. Barnhill]

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.

 

[Pat Garard]

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

______________________________________

 

[Suzanne S. Barnhill]

I'll have to chew on that a bit. I don't round any more because my hourly
rate is evenly divisible by 60.

 

[Jezebel]

Time values are precise to 5 decimal places -- ie one millisecond -- so that
shouldn't be an issue.

I'll have to chew on that a bit. I don't round any more because my hourly
rate is evenly divisible by 60.

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

______________________________________

 

[macropod]

Hi Taz Man,

You can indeed add times in Word, it just gets very complex. For an idea of
what would be required to add just two times, see the topics on time
calculations in the attachment at:
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=249902
(url all one line)

Cheers