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

--
"They say people don't believe in heroes any more...
Well damn them!!!... You and me, Max.
We gonna give'm back their heroes!"

Hi
just multiply this value with 24 and format the resulting cell as
number

--
Regards
Frank Kabel
Frankfurt, Germany


Mike Torello D.E.A. wrote:
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

Mike Torello D.E.A. wrote:
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

Hi
just multiply this value with 24 and format the resulting cell as
number


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

--
"They say people don't believe in heroes any more...
Well damn them!!!... You and me, Max.
We gonna give'm back their heroes!"

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

--
Regards
Frank Kabel
Frankfurt, Germany


Mike Torello D.E.A. wrote:
Mike Torello D.E.A. wrote:
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

Hi
just multiply this value with 24 and format the resulting cell as
number


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

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

--
Regards
Frank Kabel
Frankfurt, Germany


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

--
"They say people don't believe in heroes any more...
Well damn them!!!... You and me, Max.
We gonna give'm back their heroes!"

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.

Mike Torello D.E.A. wrote:

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

--
"They say people don't believe in heroes any more...
Well damn them!!!... You and me, Max.
We gonna give'm back their heroes!"

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?

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.

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!
--
"They say people don't believe in heroes any more...
Well damn them!!!... You and me, Max.
We gonna give'm back their heroes!"