How to multiply time?

ZenMasta

unread,

Jan 6, 2010, 3:11:56 PM1/6/10

to

I'm trying to do a quick and dirty time sheet where all I enter is hours
worked and hourly rate.

If the hours were whole numbers this would be simple but if someone works 40
hours 28 minutes
That would be 40.4666666...

I guess one way would be to have the whole numbers in one column and the
partials in another and then add them both once I convert the partials from
60th's to 100ths

T. Valko

unread,

Jan 6, 2010, 3:26:25 PM1/6/10

to

Try this...

A1 = total time in [h]:mm format as a true Excel time value
B1 = hourly rate

=A1*24*B1

You will probably want to round the result to 2 decimal places:

=ROUND(A1*24*B1,2)

Format as General or Number

--
Biff
Microsoft Excel MVP

"ZenMasta" <m...@nospam.kthx> wrote in message
news:ek3Kwxwj...@TK2MSFTNGP05.phx.gbl...

ZenMasta

unread,

Jan 6, 2010, 3:46:53 PM1/6/10

to

Column A is formated as General
When I type 50:11 it turns into 50:11:00 and in the formula box it says
1/2/1900 2:11:00 AM

"T. Valko" <biffi...@comcast.net> wrote in message
news:efHVG6wj...@TK2MSFTNGP02.phx.gbl...

Harald Staff

unread,

Jan 6, 2010, 3:56:52 PM1/6/10

to

It's the same thing; 2 days 2 hours something. Microsoft chose that
existance began new year to 1900. See
http://www.cpearson.com/Excel/datetime.htm on theory.

Format as timeformat 37:30:55 if that's an option, or custom format
[hh]:mm:ss, all cells that needs to display >23:59:59.

HTH. Best wishes Harald

"ZenMasta" <m...@nospam.kthx> wrote in message

news:u8w2RFxj...@TK2MSFTNGP05.phx.gbl...

Harald Staff

unread,

Jan 6, 2010, 4:05:46 PM1/6/10

to

PS please do not post anything if you deep dive into this and discover that
1900 was not a leap year but Excel says it was.

Best wishes Harald

ZenMasta

unread,

Jan 6, 2010, 4:40:41 PM1/6/10

to

I ended up doing it the original way I thought because this is taking longer
than I wanted for something I thought would be simple.
for example.
50 hours 11 minutes. entered as 50:11

I set custom formatting as [hh]:mm:ss
In the cell it appears as 0:50:11 in the formula bar it appears as 12:50:11
AM

"Harald Staff" <nos...@not.invalid> wrote in message
news:ODQULLxj...@TK2MSFTNGP06.phx.gbl...

Fred Smith

unread,

Jan 6, 2010, 5:31:22 PM1/6/10

to

Are you sure you entered 50:11, exactly as shown? When I do this, Excel
accepts it as 50 hours and 11 minutes, and shows 1900-01-02 02:11:00 in the
formula bar.

Obviously Excel is assuming you entered 50 minutes and 11 seconds. So you
have to convince it otherwise. Try entering 50:11:00 and see if that solves
your problem.

Regards,
Fred

"ZenMasta" <m...@nospam.kthx> wrote in message

news:eJ9HWjxj...@TK2MSFTNGP04.phx.gbl...

Jordon

unread,

Jan 6, 2010, 7:05:57 PM1/6/10

to

ZenMasta wrote:
> I ended up doing it the original way I thought because this is taking longer
> than I wanted for something I thought would be simple.
> for example.
> 50 hours 11 minutes. entered as 50:11
>
> I set custom formatting as [hh]:mm:ss
> In the cell it appears as 0:50:11 in the formula bar it appears as 12:50:11
> AM

I think you mistyped. I get 50:11:00.

Format it as [hh]:mm then type in 50:11. The result should be
01/02/1900 2:11:00 AM. But when you enter (in another cell)
=a1*24 and format it as a number, the result will be 50.18,
which is what you're looking for. That's assuming that the
time is in A1.

If you want to calculate pay but you have to calc overtime you
can have a formula that says =if(b1*24>40,40,round(b1*24,2))
and it'll never show more than 40 (for straight time). Then in
another cell enter =IF(B1*24>40,ROUND(B1*24-40,2),0) and that
will only be time that exceeds 40 hours. Then you can calc pay
rate times straight time hours and time and a half times
overtime hours.

--
Jordon