How to multiply time?

  • Thread starter Thread starter ZenMasta
  • Start date Start date
Z

ZenMasta

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
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 said:
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 said:
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

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


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
Click to expand...
Click to expand...
Click to expand...
 
ZenMasta said:
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
Click to expand...

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.
 
Back
Top