time functions
Posted by Nuno on September 02, 2001 7:15 AM
Hi, Bill.
I'm calling you from Portugal, Lisbon.
My name is Nuno and i'm experience some dificulties in a excel task i need.
If you can help me my problem is:
I've got a timetable where i put in and out of office hours.
I need to find in a in -out range, whitch hours are Extra.
From 08:00 till 21:59 normal time.
From 22:00 till 07:59 extra time.
How can i know if i'm in at 14:00 and out at 23:59 that i have 6 normal hours and 1:59 extra hours.
If you could help me i'll be most greatfull.
Thank's
Nuno
Posted by Mark W. on September 02, 2001 8:01 AM
Actually, if you're "In" at 14:00 and "Out" at 23:59
you'll have 8 "normal" hours. Assuming that your
"In" times are placed in column A and your "Out"
times are placed in column B...
=(MIN("22:00"+0,B1)-A1)*24 are your "normal" hours
=(B1-MIN("22:00"+0,B1))*24 are your "extra" hours
If you wish to diplay as 0:00 then use...
=(MIN("22:00"+0,B1)-A1) for your "normal" hours
=(B1-MIN("22:00"+0,B1)) for your "extra" hours
...and format as [h]:mm
