If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
The Sum Of Time
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. |
#3
|
|||
|
|||
The Sum Of Time
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. |
#4
|
|||
|
|||
The Sum Of Time
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. |
#5
|
|||
|
|||
The Sum Of Time
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. |
#6
|
|||
|
|||
The Sum Of Time
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 ______________________________________ |
#7
|
|||
|
|||
The Sum Of Time
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 ______________________________________ |
#8
|
|||
|
|||
The Sum Of Time (Thank You)
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 ______________________________________ . |
#9
|
|||
|
|||
The Sum Of Time
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 ______________________________________ |
#10
|
|||
|
|||
The Sum Of Time
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 ______________________________________ |
|
Thread Tools | |
Display Modes | |
|
|