Timesheet, meal rates within variables of time

[Ben]

I can claim for meals within these rules

Breakfast at £6 if I start before 07:30
Lunch at £7 for working less than 10hrs
Evening meal at £11 for working after 18:30

But I can only claim for breakfast OR evening meal NOT BOTH

Therefore since the evening meal is worth more...

I have started along the lines of an =IF formula but have come unstuck

I started with =IF((C6<TIMEVALUE("07:30")),6,0) for the breakfast
cell.

This gives me the problem that when the time cell is empty it shows I
can claim £6 for breakfast, as well as not taking into account that if
an evening meal can be claimed to show nothing.

 

[joeu2004]

I can claim for meals within these rules
Breakfast at £6 if I start before 07:30
Lunch at £7 for working less than 10hrs
Evening meal at £11 for working after 18:30
But I can only claim for breakfast OR evening meal NOT BOTH
Therefore since the evening meal is worth more...

The rules do not make good sense. I will explain below. But I think
the following will meet those requirements.

Assume that A1 and B1 contain the shift start and end "times".
However, I suggest they contain date and time, even if you format it
to show only time. That simplifies computation if the shift crosses
midnight (i.e. B1 is the day after A1). Then:

breakfast:
=IF(COUNT(A1,B1)<>2,"",
IF(AND(A1<INT(A1)+"07:30",B1<INT(A1)+"18:30"),6,0))

lunch:
=IF(COUNT(A1,B1)<>2,"",IF(B1-A1<TIME(10,0,0),7,0))

dinner:

This gives me the problem that when the time cell is empty it shows I
can claim £6 for breakfast, as well as not taking into account that if
an evening meal can be claimed to show nothing.

Those formulas return zero if you cannot claim the meal. If you
prefer "nothing", change the last zero to the null string ("").

Now for why the rules do not make sense, here are few examples.

1. If you start at 9:00 and end at 19:00, you can claim only dinner,
not lunch. I get that you employer might want to limit your claims to
only one meal. But....

2. If you start 9:30 and end at 19:00, you can claim lunch as well as
dinner.

3. Also, you if start at 15:00 and end at 23:00, you can claim lunch
as well as dinner. That may or may not be odd, depending on
applicable labor laws.

 

[Ben]

The rules do not make good sense.  I will explain below.  But I think
the following will meet those requirements.

Assume that A1 and B1 contain the shift start and end "times".
However, I suggest they contain date and time, even if you format it
to show only time.  That simplifies computation if the shift crosses
midnight (i.e. B1 is the day after A1).  Then:

breakfast:
=IF(COUNT(A1,B1)<>2,"",
IF(AND(A1<INT(A1)+"07:30",B1<INT(A1)+"18:30"),6,0))

lunch:
=IF(COUNT(A1,B1)<>2,"",IF(B1-A1<TIME(10,0,0),7,0))

dinner:


Those formulas return zero if you cannot claim the meal.  If you
prefer "nothing", change the last zero to the null string ("").

Now for why the rules do not make sense, here are few examples.

1. If you start at 9:00 and end at 19:00, you can claim only dinner,
not lunch.  I get that you employer might want to limit your claims to
only one meal.  But....

2. If you start 9:30 and end at 19:00, you can claim lunch as well as
dinner.

3. Also, you if start at 15:00 and end at 23:00, you can claim lunch
as well as dinner.  That may or may not be odd, depending on
applicable labor laws.

Thank you for your quick response, its really appreciated.

Sorry for my lack of clarity. Lunch can be claimed everyday worked
under
10 hours, so I only have to got to work to get lunch pay. I can only
have
breakfast or evening meal. So two meals a day, lunch and then
breakfast
or evening meal.

 

[joeu2004]

Sorry for my lack of clarity. Lunch can be claimed everyday
worked under 10 hours, so I only have to got to work to get
lunch pay.

No lack of clarity. I understood the first part (if "worked under 10
hours"). It is the second part ("only have to get to work") that is
not true, which surprises me.

As I demonstrated, if you work more than 10 hours, you do not get paid
for lunch. That seems odd to me.

No matter, if thems the rules. I just wanted to be sure you stated
them correctly because they did not, and still do not, make sense to
me.

 

[Ben]

No lack of clarity.  I understood the first part (if "worked under 10
hours").  It is the second part ("only have to get to work") that is
not true, which surprises me.

As I demonstrated, if you work more than 10 hours, you do not get paid
for lunch.  That seems odd to me.

No matter, if thems the rules.  I just wanted to be sure you stated
them correctly because they did not, and still do not, make sense to
me.

I see what you mean, re-read the booklet. I can claim lunch whatever
"I only have to got to work to claim lunch". It was very late when I
posted this and was obviously not switched on. So, lunch can be
claimed whatever. breakfast or dinner can be claimed for over 10 hours
but not both.

Heres what the booklet says:

Less than 10 hours a lunch may be claimed.
More than 10 hours a lunch and a breakfast or evening meal may be
claimed as appropriate.

Feel pretty silly now...

Sorry for messing you about.

 

[joeu2004]

So, lunch can be claimed whatever. breakfast or dinner can
be claimed for over 10 hours but not both.

Heres what the booklet says:
Less than 10  hours a lunch may be claimed.
More than 10 hours a lunch and a breakfast or evening
meal may be claimed as appropriate.

Not sure if what I provided you before is sufficient for your needs;
i.e. you know how to modify them accordingly. Or if you are still
looking for a solution.

If the latter, the conditions for breakfast and dinner are no longer
clear to me. Do the rules related to 18:30 still apply? Do they
apply inconjunction with the 10-hour rule above? Or does the 10-hour
above replace the 18:30-rules that you stated previously?

Note: I need further explanation only if you need further assistance.

 

[Ben]

Not sure if what I provided you before is sufficient for your needs;
i.e. you know how to modify them accordingly.  Or if you are still
looking for a solution.

If the latter, the conditions for breakfast and dinner are no longer
clear to me.  Do the rules related to 18:30 still apply?  Do they
apply inconjunction with the 10-hour rule above?  Or does the 10-hour
above replace the 18:30-rules that you stated previously?

Note:  I need further explanation only if you need further assistance.

I would like some more help thanks.

Both aplly after ten hours I can claim breakfast or dinner, but not
both with breakfast before 07:30 and dinner after 18:30

 

[joeu2004]

I would like some more help thanks.
Both aplly after ten hours I can claim breakfast or dinner, but
not both with breakfast before 07:30 and dinner after 18:30

I think the following meets your needs as I understand them. Pay
close attention to the notes that follow.

breakfast:
=IF(COUNT(A1,B1)<>2,"",
IF(AND(B1-A1>=TIME(10,0,0),A1<INT(A1)+"07:30",B1<=INT(A1)+"18:30"),
6,0))

lunch:
=IF(COUNT(A1,B1)<>2,"",7)

dinner:
=IF(COUNT(A1,B1)<>2,"",
IF(AND(B1-A1>=TIME(10,0,0),B1>INT(A1)+"18:30"),11,0))

Notes:

1. Assumes that A1 and B1 contain the start and end "times".

2. Assumes that A1 and B1 actually contain date and time, which may be
formatted to display only time. This allows for the case when the
shift crosses midnight; i.e. B1 is the day after A1. If you choose to
put only time with date, you can simplify the formulas by omitting
"INT(A1)+".

3. I have interpreted the rules to be:

a. You get lunch as long as you work that day -- that is, there is
a start and end time.

b. You get breakfast or dinner if you work 10 hours or more. You
had said only "more than 10 hours". If you should not get breakfast
or dinner if you work exactly 10 hours (or less), change "B1-A1>=" to
"B1-A1>" in the breakfast and dinner formulas.

c. You get breakfast if you start work before 7:30 and end work at
18:30 or before (and you work 10 hours or more). You had said you get
dinner only if you work "after 18:30". If you meant 18:30 or later,
change "B1<=" to "B1<" in the breakfast formula.

d. You get dinner if you end work after 18:30 (and you work 10
hours or more). Again, if you meant 18:30 or later, change "B1>" to
"B1>=" in the dinner formula.

4. Test the formulas with at least the following examples to be sure
they are behaving the way you expect. If not, please tell me which
examples fail and what you believe the results should be.

a. A1=7:30, B1=18:30
b. A1=7:29, B1=18:30
c. A1=7:29, B1=18:31
d. A1=8:31, B1=18:31
e. A1=8:32, B1=18:31
f. A1=7:29, B1=17:29
g. A1=11/20/2010 18:31, B1=11/21/2010 7:29