calculating hours

[GFH]

Hi all,

I have a series of times I am trying to add up.

For ease of explanation I am trying to add up hours worked to arrive
at a total number of hours and then multiply it by the hourly rate to
come up with the total amount to be paid.

I cannot seem to find the magic formatting/formula to do this.

I have been formating the time/hours as [h]:mm and the rate as
1,234.00, what i get returned is not a figure I can use.

When I hilight the field with the hours worked lets say it's 8:30,
8:30:00AM is displayed in the edit box.

I want the field to contain 8 hours and 30 minutes and not a time of
day. I then want to add up a series of hours worked to arrive at a
total number of hours and then multiply this by a number to arrive at
a monetary figure

What I want is

eg 8:30 +7:15+5:45 = 21:30 hours

and then

21:30*75.00 to get $1,612.50

What I get is

21:30 * 75.00, I get 67:19 returned.

Many Thanks

 

[Peo Sjoblom]

You must be a lawyer with those rates?

Try

=Total_time*24*rate

or even

=ROUND(Total_time*24*rate,2)

(to round to nearest 2nd decimal)

format result as currency

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[GFH]

Hi Peo

That was easy enough. I saw earlier on a reference to someones website
which I am wading my way through but your solution worked just fine
until I figure out the finer details.

I'll try Mirella's as well for my own edification. Thanks Mirella

No not a lawyer, I have too many friends to be one I fly
airplanes...

Cheers and thanks

 

[Peo Sjoblom]

Thanks for the feedback, you are one of the rare people that post back

Quick explanation. Excel sees one day as 1, thus 1 hour is 1/24

=1/24

returns 0.0416666666666667

format that as time hh:mm and you will get 01:00

that also means to convert a time value like 01:00 to the decimal equivalent
1.0 you need
to multiply by 24, thus 10:30 * 24 returns 10.5 formatted as general, then
just multiply by the rate

No need to look at the other solution you got since it won't work for +24
hours

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[mireillea]

Hi,
Try to use function hour() and function minute() that converts the hour and
minute to numbers
ex:
lets say cell a1 contains 21:30 formatted as date and time
then type in a new cell the following:
=sum(hour(a1),minute(a1)/100)*75.00

 

[GFH]

Thanks again Peo,

I came across somebodies website firget the nam eoff the top of my
head and it explained the theory.

I still have to refer back and forth to make sure I get it right but
it seams straight forward enough.


Hey if no one says thanks then the people we depend on, people like
you who know the answer, won't answer anymore, so thank you again (and
the rest of you folks who answer).

You guys have saved me a whole bunch of frustation on more than one
ocasion..

Cheers