IF Function?-Start Time

**mizzamzz** · 06-30-2010, 11:20 PM

Hi All!!

Firstly let me just say this forum is AWESOME! What a fab idea!!!

I was hoping someone may be able to help me with a particular IF formula?

It's for a time sheet and what I would ideally like to do is work apply time and a half to any hours worked outside 8am-8pm. How can I do this?

For example how would I do a formula to populate cell C in the below?

Start Time(Cell A) Finish Time (Cell B) Overtime Hours (Cell C)

06:30 21:45

I need to do something along the lines of: If Cell A is less than 8am then the hours less than 8am need to be multiplied by .5 (as the total number of hours is cell C will be added to my total hours for the week in another formula which I already have in place). Then if Cell B is greater than 20:00 then the hours greater than this need to be multiplied by .5 also.

Is this asking too much? Can this even be done?

Any help would be HUGELY appreciated!!

**DonkeyOte** · 07-01-2010, 02:10 AM

First, can I ask that you retitle your thread to something more befitting the question at hand - eg: "calculating overtime hours"
(apt titles help people with similar issues find solutions on the board more easily)

Assuming that shifts can cross midnight then perhaps:

C2:
=IF(COUNT($A2:$B2)<2,"",0.5*IF($B2>=$A2,MAX(0,$E$2-$A2)+MAX(0,$B2-$F$2),(1-MAX($A2,$F$2))+MIN($E$2,$B2)))
copied down

where Col A holds start, Col B end and E2 & F2 hold shift times (eg 08:00, 20:00 respectively)

C2 onwards format as Time as appropriate

NOTE: we are assuming that the values in A/B are time only and not datetime values...

IF Function?-Start Time

LinkBack

Thread Tools

Rate This Thread

Display

IF Function?-Start Time

re: IF Function?-Start Time

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions