calculate cells with format hh:mm

R

Rockbear

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
IN OUT HOURS AFTER 18

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??
 
J

JE McGimpsey

One way:

D2: =MAX(0,B2-TIME(18,0,0))

or, equivalently:

D2: =MAX(0, B2-0.75)

Format D2 as time.
 
R

Rockbear

Thank you it worked, the =MAX(0, B2-0.75) worked but could not make the other
one work.
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

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

Thanks for the efford, and I clicked YES on your first reply, and help :)
 
J

JE McGimpsey

It works because XL stores times as fractional days. 18:00 is 18/24ths
of a day, or 0.75.

No idea what "I clicked YES" means, but thanks, I guess...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top