Calculating Lunch times of 0, 30min, 60min

[sonar]

Hi, I have a spreadsheet with cells formated as hh:mm. One cell is for
the start time, and the next cell is for the end time.

Then there is a third cell where you specify if the person takes 30min
or 60min lunch, depending on how long they have worked for the day. I
need to automate this calculation for lunches.

eg.

1. If total hours worked was less than 6hrs, no lunch is recorded in
the third cell.

2. If total hours worked is more than 6hrs, but less than 6.75hrs,
30min is recorded in the third cell

3. If total hours worked is 6.75hrs and more, 60min is recorded in the
third cell.

I tried the IF statment as follows:
=IF((B1-A1)>VALUE("06:00"),30,0)

But its not doing the job I want, I know I might be on the right track,
but it needs more than this.

Can someone please help.

Sonar

 

[Arvi Laanemets]

Hi

A general solution (working time can reach over midnight):
=((B2-A2+(B2<A2)>6/24)+(B2-A2+(B2<A2)>6.75/24))*30/(24*60)
and format as "h:mm" or "[mm]"

When both starting and end time are in same day, then the simpler version of
formula is:
=((B2-A2>6/24)+(B2-A2>6.75/24))*30/(24*60)

 

[sonar]

Thanks Arvi Laanemets

It really helped me.

Sona