This is great! Everything works perfectly! However, a co-worker reminded me
that my spreadsheet isn't done yet. I need to limit the total hours worked
in the SUM of Cells J9 through J15 not only to 8 hours per day, but also to
40 hours per week. Any excess of 8 hours per day AND/OR 40 hours per week
needs to be thrown into Cells Q9 thru Q15.
I.e., Sunday (the 1st day listed in the timesheet) I work 8 hours, J9
reflects 08:00; Monday thru Thurs I work 9.5 hours each day, J10 thru J13
reflects 08:00 each and Q10 thru 13 reflects 01:30 each; Friday I work 9.5
hours but these hours put me over 40 hours worked this week, so J14 should
reflect 00:00 and Q14 should reflect 09:30; subsequently Saturday's hours
would also be all overtime.
Can the formula you gave me be altered for, say Wed thru Sat, to reflect any
time over 40 accumulated hours that week as overtime in Column Q?
--
Thank you, again, for your time, effort and expertise in replying to this
additional challenge.
Bonnie
"Pete_UK" wrote:
> If your total hours are in I9, then put this formula in J9:
>
> =FLOOR(MIN(I9,8/24),15/24/60)
>
> to give you a maximum of 8 hours, and this formula in K9:
>
> =IF(I9<8/24,0,FLOOR(I9-8/24,15/24/60))
>
> to give you the remainder, rounded down. Format both cells as
> appropriate.
>
> Hope this helps.
>
> Pete
>
>
> On Mar 3, 6:38 pm, Bonnie
discussions.microsoft.com> wrote:>> I must have done something wrong. My total hours for one example day are
>> 10:30 (10 hours 30 minutes). When I put your formula in the next cell, it
>> calculated 0:33 (which would read as 33 minutes rather than the 8 full hours
>> needed, shown as 08:00).
>>
>> In my initial question I used hypothetical cells; here is what I actually
>> have:
>>
>> I9 = totaled hours worked for the day (C9 thru H9 reflect the "in" and "out"
>> times)
>> J9 = regular pay hours worked (up to and including 8 full hours ---
>> reflected as 08:00)
>> Q9 = paid overtime (hours worked for the day above 8 full hours ---
>> reflected as 02:30 for two hours and 30 minutes)
>>
>> All hours are reflected in military time (24-hour clock). The time
>> reflected in Cell I9 is actual minutes worked (i.e., 10:40); the time
>> reflected in Cells J9 and Q9 have to be rounded DOWN to the nearest worked
>> quarter hour (08:00 in J9 and 02:30 in Q9).
>>
>> I haven't done complicated formulas in years so, if you don't mind, can you
>> spell it out for me?
>> --
>> Thank you for your time and effort in replying to my question.
>>
>> Bonnie
>>
>>
>>
>> "Pete_UK" wrote:
>>> Assume you are doing this on row 10 - put this formula in H10:
>>
>>> =FLOOR(MIN(G10,8/24),15/24/60)
>>
>>> and this in I10:
>>
>>> =IF(G10<8/24,0,FLOOR(G10-8/24,15/24/60))
>>
>>> By way of explanation - 8/24 relates to 8 hours (compared with a 24-hour
>>> day), and 15/24/60 relates to 15 minutes.
>>
>>> If you are on a different row, then change G10 as appropriate.
>>
>>> Hope this helps.
>>
>>> Pete
>>
>>> "Bee"
discussions.microsoft.com> wrote in message
>>>news:8262EC4F-EFA8-4AC3-9FC3-E6CA3D195BE0@microsoft.com...>>>> I've reviewed numerous posts on timesheets and cannot find what I need..
>>>> If
>>>> you can help, please do.
>>
>>>> Cell G totals daily time logged in (in, out, in, out) from times entered
>>>> in
>>>> cells A thru F. This part I got. I want cell H to display only the hours
>>>> up
>>>> to and including "08:00" --- rounded DOWN to the nearest quarter hour.
>>>> Any
>>>> excess time (over 8 full hours) I want displayed in cell I --- also
>>>> rounded
>>>> DOWN to the nearest quarter hour. Ex: If G displays a total of 09:40 (9
>>>> hours 40 mintues), I want cell H to display 08:00 and cell I to display
>>>> 01:30; if G displays only 06:25, I want H to display 06:15 and cell I to
>>>> display 00:00.
>>
>>>> (This was even complicated for me to explain let alone know how to make it
>>>> happen!)- Hide quoted text -
>>
>> - Show quoted text -
>
>
