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!)

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" wrote in message
...
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!)

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" wrote in message
...
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!)

Look again at the result you've got. My suspicion is that you don't have
0:33 (with a colon), but 0.33 (with a decimal point). 0.33 of a day is 8
hours. If you format the cell not as number or general but as time (for
example as [hh]:mm), then you'll see it as 08:00.
--
David Biddulph

"Bonnie" wrote in message
...
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" wrote in message
...
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!)

You are right. Boy do I feel stupid! I could chauk it up to being retired
and blaming the cobwebs in my brain, but ...
--
Thank you, again, for your time and effort in replying to my question.

Bonnie


"David Biddulph" wrote:

Look again at the result you've got. My suspicion is that you don't have
0:33 (with a colon), but 0.33 (with a decimal point). 0.33 of a day is 8
hours. If you format the cell not as number or general but as time (for
example as [hh]:mm), then you'll see it as 08:00.
--
David Biddulph

"Bonnie" wrote in message
...
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" wrote in message
...
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!)

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 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" wrote in message
...
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 -

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 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" wrote in message
...
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 -

It sounds like you work too much, Bonnie !!

Your Wednesday cells should be on row 12, so try this in J12:

=IF(SUM(J$9:J11,I12)=40/24,MAX(0,40/24-SUM(J
$9:J11)),FLOOR(MIN(I12,8/24),15/24/60))

and this in Q12 (if that is where you have moved the formula to):

=IF(I12<8/24,0,FLOOR(I12,15/24/60)-J12)

Copy both formulae down to cover Thursday to Saturday cells.

Hope this helps.

Pete

On Mar 4, 5:58*pm, Bonnie wrote:
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 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" wrote in message
...
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 -- Hide quoted text -

- Show quoted text -