-----Original Message-----
What are they, decimal hour or decimal minutes?
If they are hours just use
=INT(SUM(A1:G1)) for hours and
=MINUTE(MOD(SUM(A1:G1),1)/24)
for minutes. If you want everything in time format
=SUM(A1:G1)/24
format as [h]:mm
if they are minutes
=SUM(A1:G1)/1440
format as [h]:mm
for hours
=INT(SUM(A1:G1)/60)
and minutes
=MINUTE(MOD(SUM(A1:G1)/60,1)/24)
--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.
Regards,
Peo Sjoblom
Hello from Steved
469.30 1383.34 1383.08 1386.17 1383.34 1392.29 658.35
How would I go about creating a formula for the above into
total hours and minutes, yes the above is decimal points
.
-----Original Message-----
What are they, decimal hour or decimal minutes?
If they are hours just use
=INT(SUM(A1:G1)) for hours and
=MINUTE(MOD(SUM(A1:G1),1)/24)
for minutes. If you want everything in time format
=SUM(A1:G1)/24
format as [h]:mm
if they are minutes
=SUM(A1:G1)/1440
format as [h]:mm
for hours
=INT(SUM(A1:G1)/60)
and minutes
=MINUTE(MOD(SUM(A1:G1)/60,1)/24)
--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.
Regards,
Peo Sjoblom
Hello from Steved
469.30 1383.34 1383.08 1386.17 1383.34 1392.29 658.35
How would I go about creating a formula for the above into
total hours and minutes, yes the above is decimal points
.
Hello from Steved
469.30 1383.34 1383.08 1386.17 1383.34 1392.29 658.35
How would I go about creating a formula for the above into
total hours and minutes, yes the above is decimal points
From a later post of yours, it appears as if what you are calling "decimal
points" really is "minutes". So that 469.30 really means 469 hrs and 30
minutes.
Is that so?
If so, then you can use the DOLLARDE & DOLLARFR functions from the Analysis
Tool Pak.
For example, if one number is in A1, and another in A2, then:
=DOLLARFR(DOLLARDE(A1,60)+DOLLARDE(A2,60),60)
will give the sum.
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.
On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
If you need to add more than two, then you might be better off writing your own
formula or function which can accept arrays.
For a formula that will add your numbers, and return the answer in the same
format, the *array-entered* formula:
=INT(SUM(INT(A1:A7)+MOD(A1:A7,1)*100/60))+
MOD(SUM(INT(A1:A7)+MOD(A1:A7,1)*100/60),1)*60/100
will do it. Your examples add to 8057.07 (8,057 hrs and 7 minutes)
You could also use a helper column, and the DOLLARDE function. THen sum the
helper column and use the DOLLARFR function on the result.
-----Original Message-----
Hi
to convert these numbers to Excel time format you may use the formula
=INT(A1)/24+MOD(A1,1)/(24*60)
and format this cell with the custom format
[hh]:mm
now you can simply add these converted figures
--
Regards
Frank Kabel
Frankfurt, Germany
.Hello from Steved
469.30 1383.34 1383.08 1386.17 1383.34 1392.29 658.35
How would I go about creating a formula for the above into
total hours and minutes, yes the above is decimal points
-----Original Message-----
result.
Oh, and here's another way using text manipulations:
=SUM(TIMEVALUE(SUBSTITUTE(TEXT(A1:A7,"#.00"),".",":")))
The formula needs to be array-entered. Hold down
<enter>.
Format the result as [h].mm
--ron
.
Thankyou Ron
Ron you have introduced me to Dollarfr,Dollarde,
Because I receive from another Company 1.34 instead off
1:34 you have opened a new window of opportunity for me.
I thankyou for taking timeout on this.
Have a good week
Cheers.