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