Timesheet question

[iammetx]

I'm trying to calculate the meals taken within a time frame. The client inputs time in and time out like this:

<table width="152" border="0" cellpadding="0" cellspacing="0" height="93"><col style="width: 48pt;" width="64" span="2"> <tbody><tr><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top" align="center">G
</td><td valign="top" align="center">H
</td></tr><tr style="height: 15pt;" height="20"> <td valign="top" align="right">9
</td><td valign="top">
</td><td valign="top">
</td><td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Time</td> <td class="xl65" style="width: 48pt;" width="64">Time</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top" align="right">10
</td><td valign="top">
</td><td valign="top">
</td><td class="xl66" style="height: 15pt; width: 48pt;" width="64" height="20">8:00</td> <td class="xl67" style="width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% yellow;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top" align="right">11
</td><td valign="top">
</td><td valign="top">
</td><td class="xl68" style="height: 15pt; width: 48pt; font-size: 10pt; color: white; font-weight: 400; text-decoration: none; font-family: Arial; border: 1pt solid windowtext; background: none repeat scroll 0% 0% yellow;" width="64" height="20">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">5:00</td> </tr> </tbody></table>
Sorry, that's the format they want it in with G10 being time in and H11 being time out.

So anyways, here are the times for each meal:
Breakfast is from 7:30-9:30
Lunch is from 11:30-12:30
PM snack is from 1:30-2:30

I created a table (which doesn't look good here) for the meal times because it is subject to change, so here is the what the table for the meal times looks like:

<table width="384" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 15pt;" height="20"> <td valign="top">
</td><td class="xl65" style="height: 15pt; width: 48pt;" width="64" align="center" height="20">U</td> <td class="xl65" style="width: 48pt;" width="64" align="center">V</td> <td class="xl65" style="width: 48pt;" width="64" align="center">W</td> <td class="xl65" style="width: 48pt;" width="64" align="center">X</td> <td class="xl65" style="width: 48pt;" width="64" align="center">Y</td> <td class="xl65" style="width: 48pt;" width="64" align="center">Z</td> </tr> <tr style="height: 15.6pt;" height="21"> <td valign="top">6
</td><td colspan="2" class="xl69" style="height: 15.6pt;" align="center" height="21">Breakfast</td> <td colspan="2" class="xl69" style="border-left: medium none;" align="center">Lunch</td> <td colspan="2" class="xl69" style="border-left: medium none;" align="center">PM</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">7
</td><td class="xl66" style="height: 15pt; border-top: medium none;" align="center" height="20">7:30</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="center">9:30</td> <td class="xl66" style="border-top: medium none;" align="center">11:30</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="center">12:30</td> <td class="xl66" style="border-top: medium none;" align="center">1:30</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">2:30</td> </tr> </tbody></table>
Now, if according to the sign in/sign out, if a client was present for any of these meals, then an "X" is input, if not, then a 0.
For example, 8:00-5:00
<table width="384" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td class="xl69" style="width: 48pt;" width="64">
</td> <td class="xl69" style="width: 48pt;" width="64">
</td> <td class="xl69" style="width: 48pt;" width="64" align="center">G</td> <td class="xl69" style="width: 48pt;" width="64" align="center">H</td> <td class="xl70" style="width: 48pt;" width="64" align="center">I</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" align="right" height="20">39</td> <td colspan="2" class="xl66" style="border-width: 1pt medium 1pt 1pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% yellow;">
</td> <td class="xl65" style="border-left: medium none;" align="center">Brkfst</td> <td class="xl65" style="border-left: medium none;" align="center">Lu</td> <td class="xl72" style="border-left: medium none;" align="center">Pm</td> </tr> <tr style="height: 14.4pt;" height="19"> <td class="xl73" style="height: 14.4pt;" align="right" height="19">40</td> <td colspan="2" class="xl74">Client Name</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="center">X
</td> <td class="xl75" style="border-top: medium none; border-left: medium none;" align="center">X
</td> <td class="xl76" style="border-top: medium none; border-left: medium none;" align="center">X
</td> </tr> </tbody></table>

Example, 10:30-1:25
<table width="384" border="0" cellpadding="0" cellspacing="0"><tbody><tr height="19"></tr><tr style="height: 15pt;" height="20"><td class="xl68" style="height: 15pt; width: 48pt;" width="64" height="20">
</td><td valign="top">
</td> <td class="xl69" style="width: 48pt;" width="64">
</td><td class="xl69" style="width: 48pt;" width="64">
</td><td class="xl69" style="width: 48pt;" width="64" align="center">G</td><td class="xl69" style="width: 48pt;" width="64" align="center">H</td><td class="xl70" style="width: 48pt;" width="64" align="center">I</td></tr><tr style="height: 15pt;" height="20"><td class="xl71" style="height: 15pt;" align="right" height="20">39</td><td valign="top">
</td> <td colspan="2" class="xl66" style="border-width: 1pt medium 1pt 1pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; background: none repeat scroll 0% 0% yellow;">
</td><td class="xl65" style="border-left: medium none;" align="center">Brkfst</td><td class="xl65" style="border-left: medium none;" align="center">Lu</td><td class="xl72" style="border-left: medium none;" align="center">Pm</td></tr><tr style="height: 14.4pt;" height="19"><td class="xl73" style="height: 14.4pt;" align="right" height="19">40</td><td valign="top">
</td> <td colspan="2" class="xl74">Client Name</td><td class="xl75" style="border-top: medium none; border-left: medium none;" align="center">0
</td><td class="xl75" style="border-top: medium none; border-left: medium none;" align="center">X
</td><td class="xl76" style="border-top: medium none; border-left: medium none;" align="center">0
</td></tr></tbody></table>
There are several problems with the way the time is input, however. One is that the user does not input AM/PM because it is assumed that the hours are from 8am-5pm. I was thinking of creating a list, but the problem is that I want a simple list, and not a long one, and since the times are not rounded up, the list would be long. In other words, the user can input 8:47-2:53, so the dropdown list would start off with (8:01,8:02,:8:03,etc...all the way to 5:00). Too long!

So what I did instead was create a column that I can hide where I assign a value to every single minute within the time range. Like this:

<table width="111" border="0" cellpadding="0" cellspacing="0" height="151"><col style="width: 48pt;" width="64" span="2"> <tbody><tr><td valign="top" align="center">A
</td><td valign="top" align="center">B
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr><tr style="height: 15pt;" height="20"> <td colspan="2" class="xl65" style="height: 15pt; width: 96pt;" width="128" height="20">Time Value
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">8:00</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">1
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">8:01</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">2
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">8:02</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">3
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">8:03</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">4
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">8:04</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">5
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> </tbody></table>
etc....

But now I'm stuck on how to make all of this come together.

The formula that I had for Breakfast was in extra cell

Z1:=AND(G10>=U7,G10<=V7)

Then, in cell where value is to be added (G40),

If(Z1=True,X,0)

But that didn't work.

Then I tried Vlookup for breakfast.

Z1:=VLOOKUP(G10,A3:B571,A3:A571,B3:B571)
G40:=IF(AND((Z1<=122),(U9<>1)),"X",0)

Which works just fine if you input any time between breakfast (7:30-9:30) but gives me an error message if I try inputting other times, like 2:00 for example.

And it gets worse with lunch.

I do hope someone understands what I'm saying/trying to do and can help me out. Any help is greatly appreciated and let me know if you need more info or clarification. Thank you.

 

[Jerry Sullivan]

Hi iammetx,

If I understand your objective correctly, you should be able to eliminate the use of the helper cells. It sounds like your intent is that if a meal overlaps any part of a client's time in-time out span, you want an X for that meal. Otherwise an 0.


If you didn't have to account for AM/PM then this would work:

=IF(AND(G10<=V7,H11>=U7),"X",0)

To adjust for AM/PM based on the constraint that all times will be between 8:00 AM and 5:00 PM, then apply a conversion formula to each cell reference that might possibly be a PM value posing as an AM value.
For example for G10, substitute this expression in place of G10:

IF(G10<0.25,G10+0.5,G10)

When you substitute the conversion formulas for G10 and H11 into the original formula, your resulting formula for breakfast would be:

=IF(AND(IF(G10<0.25,G10+0.5,G10)<=V7,IF(H11<0.25,H11+0.5,H11)>=U7),"X",0)

This assumes that your meal start and finish times for breakfast would never be after 12:00. You could apply the conversions to all 4 cell references if you wanted to allow for that possibility, it just makes your formula a little harder to read. You would need to do that for all 4 references for the Lunch formula.

Good luck!