=max between one hour and second hour
 

In Out Hours
16:00 18:30 02:30
08:55 15:00 06:05
12:00 21:00 09:00
10:00 16:00 06:00
08:45 16:35 07:50
08:00 20:30 12:30

I have a hourlist like this, the hours are in format hh:mm, what I hope to
manage is..

lets call the columns
A B C D E
IN OUT HOURS AFTER 18 AFTER 21

in column D i want to have all hours after 18:00 listet automaticly, so if a
worker works to 19:30 the col. D should list 1:30
Is this possible with the HH:MM format??

I got this from a JE Mc Gimpsey

Use =MAX(0, B2-0.75)
Have two more questions :
1.
Why the -0,75?? it works but could not understand why it works
2.
IF the same formula (=MAX(0, B2-0.75) ) should be used between 18:00 and
21:00, and list only the hours between 18:00 and 21:00, and a new one from
21:00 to 23:59 , then it need to abduct the hours between 18:00 and 21:00

between 18:00 and 21:00 the get 40% additional sallary
and after 21:00 they get 100% more, would have bee SO nice to be able to
make this calculation automatic

--
Just a regular user

=max between one hour and second hour
 

0.75 = 06:00 PM or 18:00

1 equals one day equals 24 hours meaning that 1 hour equals 1/24 equals
0.041667, it also means that 12 hours equals 0.50 thus 18 hours equals 0.75

21:00 equals 0.875 written as a decimal so you can use the same technique



--


Regards,


Peo Sjoblom

"Rockbear" wrote in message
...

In Out Hours
16:00 18:30 02:30
08:55 15:00 06:05
12:00 21:00 09:00
10:00 16:00 06:00
08:45 16:35 07:50
08:00 20:30 12:30

I have a hourlist like this, the hours are in format hh:mm, what I hope to
manage is..

lets call the columns
A B C D E
IN OUT HOURS AFTER 18 AFTER 21

in column D i want to have all hours after 18:00 listet automaticly, so
if a
worker works to 19:30 the col. D should list 1:30
Is this possible with the HH:MM format??

I got this from a JE Mc Gimpsey

Use =MAX(0, B2-0.75)
Have two more questions :
1.
Why the -0,75?? it works but could not understand why it works
2.
IF the same formula (=MAX(0, B2-0.75) ) should be used between 18:00 and
21:00, and list only the hours between 18:00 and 21:00, and a new one from
21:00 to 23:59 , then it need to abduct the hours between 18:00 and 21:00

between 18:00 and 21:00 the get 40% additional sallary
and after 21:00 they get 100% more, would have bee SO nice to be able to
make this calculation automatic

--
Just a regular user

=max between one hour and second hour
 

Excel stores dates and times as numbers.

For time calculations 24 hours is treated as 1 so 18:00 hours is 0.75, 21:00
would be 0.875

You can use the following to find the hours after 18:00 and before 21:00 if
any
=IF(AND(C1Time(18,0,0),C1<Time(21,0,0),Time(21,0, 0)-C1,"")

Similarly for hours after 21:00

=IF(C1Time(21,0,0),C1-Time(21,0,0),"")

Of course it is assumed that worker is out by midnight...

Once you get the hours, you can multiply them by rate to get the total
compensation. If you have daily rate then simply multiply the hours you get,
if you have hourly rate then multiply the hours by rate and then by 24

"Rockbear" wrote:


In Out Hours
16:00 18:30 02:30
08:55 15:00 06:05
12:00 21:00 09:00
10:00 16:00 06:00
08:45 16:35 07:50
08:00 20:30 12:30

I have a hourlist like this, the hours are in format hh:mm, what I hope to
manage is..

lets call the columns
A B C D E
IN OUT HOURS AFTER 18 AFTER 21

in column D i want to have all hours after 18:00 listet automaticly, so if a
worker works to 19:30 the col. D should list 1:30
Is this possible with the HH:MM format??

I got this from a JE Mc Gimpsey

Use =MAX(0, B2-0.75)
Have two more questions :
1.
Why the -0,75?? it works but could not understand why it works
2.
IF the same formula (=MAX(0, B2-0.75) ) should be used between 18:00 and
21:00, and list only the hours between 18:00 and 21:00, and a new one from
21:00 to 23:59 , then it need to abduct the hours between 18:00 and 21:00

between 18:00 and 21:00 the get 40% additional sallary
and after 21:00 they get 100% more, would have bee SO nice to be able to
make this calculation automatic

--
Just a regular user

=max between one hour and second hour
 

Excel stores time (and dates) as a number representing days. So 18 hours is
18/24 of a day, and 18/24 =0.75. You could use =MAX(B2-18/24) However, Excel
would then give the answer 0.020833 since 30 mins is (1/2)/24 = 0.020833 of
a day. Then you need to format the cell as time - Excel sometimes gets 'over
helpful)

If someone works to 19:30 you want 1:30 in D and 0 in E
If they work to 22:45, you want 3 in D and 1:45 in E
So in D use =MIN(3/24,MAX(0, B2-18/24))
(the 3/24 is the 3 hours from 18:00 to 21:00, we divide by 24 since time is
stored as a fraction of a day)
And in E use =MAX(0,B2-21/24)
Hope this helps, please come back if you need more help
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rockbear" wrote in message
...

In Out Hours
16:00 18:30 02:30
08:55 15:00 06:05
12:00 21:00 09:00
10:00 16:00 06:00
08:45 16:35 07:50
08:00 20:30 12:30

I have a hourlist like this, the hours are in format hh:mm, what I hope to
manage is..

lets call the columns
A B C D E
IN OUT HOURS AFTER 18 AFTER 21

in column D i want to have all hours after 18:00 listet automaticly, so
if a
worker works to 19:30 the col. D should list 1:30
Is this possible with the HH:MM format??

I got this from a JE Mc Gimpsey

Use =MAX(0, B2-0.75)
Have two more questions :
1.
Why the -0,75?? it works but could not understand why it works
2.
IF the same formula (=MAX(0, B2-0.75) ) should be used between 18:00 and
21:00, and list only the hours between 18:00 and 21:00, and a new one from
21:00 to 23:59 , then it need to abduct the hours between 18:00 and 21:00

between 18:00 and 21:00 the get 40% additional sallary
and after 21:00 they get 100% more, would have bee SO nice to be able to
make this calculation automatic

--
Just a regular user