how to calcurate work hours? Please?

  • Thread starter Mike Torello D.E.A.
  • Start date
M

Mike Torello D.E.A.

I have
start time end time work time
17:00 18:00 01:00

Now, I have to convert this 01:00 to 1,00! How?

Work time is calculated on 00:15min parts
so I could have for example:

start time end time work time
17:00 18:15 01:15

And I have to convert it to 1,25

if 01:00h = 1,00
if 00:30min = 0,50
fi 00:15min = 0,25

I round it on 00:15min as smallest unit of hour!

Must have this to sum total number of work time so I can multiply it with
fee per hour... Or is there any formula what can do that directly from the
hour time

If I put work time column to sum all time, when sum is over 24h it starts
again from 0! So if total sum is example 34h it will show 10h or if its 49h
it will show only 1h....

I'll send document if someone doesn't understand what I need?

Thanx in advance
 
M

Mike Torello D.E.A.

Mike said:
Hi
just multiply this value with 24 and format the resulting cell as
number
Click to expand...


If I have sum of 49h, that's 2 days & 1hour but Excel displays it like
01:00h (but it's actually 2 days and 1hour) and if I multiply that with 24 I
will have only 24h

But it will show same result if the sum is 25h it will display 01:00h... and
if you even try to multiply 00:00 typ of noumber vith something you get some
stupid value... please try and you'll see...

If you wish I'll send you my worksheet so you can try it by yourself...
 
J

JWolf

In C1:
=INT((B1-A1)*24)+ROUNDDOWN(MOD((B1-A1)*24,1)*4,0)/4
This does not credit for any time less than 1/4 hour.
Change to =INT((B1-A1)*24)+ROUND(MOD((B1-A1)*24,1)*4,0)/4
and if 8 minutes past 1/4 hour are worked, the whole 1/4 is credited.
Change both 4s to 5s and you round to 12 minute intervals
Change both 4s to 6s and you round to 10 minute intervals
Format the cell with the formula to a number format.
 
F

Frank Kabel

Hi
first format the cell with the time with the custom format
[hh]:mm
to display hours >24
after this just multiply this cell with 24 and format the resulting
cell as number.
Note: The multiplication would do with your original cells. Though
Excel display 01:00 internally it has stored the complete value
 
M

Mike Torello D.E.A.

I have
In C1:
=INT((B1-A1)*24)+ROUNDDOWN(MOD((B1-A1)*24,1)*4,0)/4
This does not credit for any time less than 1/4 hour.
Change to =INT((B1-A1)*24)+ROUND(MOD((B1-A1)*24,1)*4,0)/4
and if 8 minutes past 1/4 hour are worked, the whole 1/4 is credited.
Change both 4s to 5s and you round to 12 minute intervals
Change both 4s to 6s and you round to 10 minute intervals
Format the cell with the formula to a number format.
Click to expand...

Time is allready rounded on 1/4 hour so dont worry about that...
I tried to enter your formula but it doesnt work ;(

Can you fix some formula for this conversion column? :

start time end time work time Conversion
17:00 18:00 01:00 1,00
17:00 19:45 02:45 2,75
17:00 18:30 01:30 1,50
17:00 19:15 02:15 1,25

If there is formula for that I'll make another column for it...


THANX a lot!
 
M

Mike Torello D.E.A.

Hi
first format the cell with the time with the custom format
[hh]:mm
to display hours >24
after this just multiply this cell with 24 and format the resulting
cell as number.
Note: The multiplication would do with your original cells. Though
Excel display 01:00 internally it has stored the complete value
Click to expand...


Thanx, I did it ;))) It's working ;)))
 
Top