If then statement

[Lionround]

Excel 2007. I have created a timesheet that calculates the number of hours worked minus half hour for lunch. So if an employee comes in at 8:30 am and leaves at 5:00 pm, they are credited 8 hours.

However, if they work 4 hours or less, I need the formula to NOT deduct the half hour.

What I have thus far is:

=IF(COUNT(C13:D13)<2,"",MIN(8.5,MOD(D13-C13,1)*24)-0.5)

C13 is arrival time and D13 is departure time.

Suggestions?

 

[AlphaFrog]

try something like this...

=IF(COUNT(C13:D13)<2,"",MIN(8.5,MOD(D13-C13,1)*24)[COLOR="Red"]-IF(MOD(D13-C13,1)*24>4,0.5,0)[/COLOR])

 

[jmthompson]

=if(count(c13:d13)<2,"",if(mod(d13-c13,1)<4.1,min(8.5,mod(d13-c13,1)*24),min(8.5,mod(d13-c13,1)*24)-0.5))

 

[Lionround]

AlphaFrog,

Worked like a charm. Thanks.