Figuring time worked, and then separating it into regular time an.

[Guest]

This should be relatively simple, but I can’t figure it out.

The function has to be setup in one cell.

First; the function needs to figure how many hours you worked from the
beginning time to the ending time.

Second; the hours worked has to be split into normal working hours and
overtime. The hours an employee can work obviously varies between full and
part-time employees, but there can not be a vlookup on the timesheet.
Although a vlookup on another worksheet in an excel workbook is acceptable.
Overtime hours will trickle off the remaining time into the next column.

Finally; this is all based on quarter hours. So the entire function has to
be rounded. I know how to use the round function, but, I can only use it in
simple problems right now.

Whenever someone has the time to help me; thanks.

Don,

 

[Bob Phillips]

For an 8 hour day

=MIN(ROUND((B1-A1)*96,0)/96,TIME(8,0,0))

gives regular time, and

=MAX(0,ROUND((B1-A1)*96,0)/96-TIME(8,0,0))

gives overtime

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks for helping so quickly.

Sadly, I not only forgot to mention some variables, and I already solved my
own problem while waiting for someone to reply. The formulas are all more
complicated, but all work perfectly. I am actually going to try and post
them in a reasonable fashion for others.

Timesheet columns are as follows from column A – I.

A – Day (Monday, Tuesday, etc.)
B – Date (10-19-06)
C – Start Time (h:mm AM/PM)
D – End Time (h:mm AM/PM)
E – Regular Hour
=IF((TEXT(((D12-C12)-G12),"h:mm"))>"8:00",("8:00"),(TEXT(((D12-C12)-G12),"h:mm")))
F – Overtime Hours
=IF(E12="8:00",TEXT((((D12-C12)-G12)-E12),"h:mm"),"0:00")
G – Peronsal/Lunch (“h:mmâ€)
H – Vacation (useless really since vacation is measured in days)
I – Day Totals Work Hours
=IF(TEXT((E12+F12-H12),"h:mm")="0:00","",TEXT((E12+F12-H12),"h:mm"))

Now I have another problem, and I think it is caused by all the “TEXT(“ in
my formulas. I try to auto-sum the hours for the week, but it always comes
up as 0:00 (“h:mmâ€). I need this to show the totals for not only all the
total days, but the totals for columns E – H. This time I need someone to
tell me what to change in my formulas without changing them entirely. These
formulas do exactly what they need to do for the full-time employee
timesheet. Not only that, but I understand everything just by looking at
them. So any help would be appreciated.

 

[Bob Phillips]

Your formulae don't work for me, and you don't round to the quarter hour as
originally asked for.

Try these in E12:F12

=MIN(ROUND((D12-C12-G12)*96,0)/96,TIME(8,0,0))

=MAX(0,ROUND((D12-C12-G12)*96,0)/96-TIME(8,0,0))

format them as time, and they will sum okay. Remember to use a format of
[h]:mm in the totals, to allow more than 24 hours.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob,

Since you seem to be the only one helping me, and by the looks of it
everyone else, thanks again. You were right about my formulas. And yours
worked almost perfectly when I first used them. 3 seconds later after
formatting the cells the way you told me to, it worked perfectly.

Problem is there is something I was not told to compensate for until earlier
today. And don’t ask me why they want this change, but they do. I will try
and make this quick and simple to save on reading time.

What the time sheet does right now.
8 AM – 4:30 PM Half an hour lunch = 8 hours 0 overtime
8 AM – 5 PM Half an hour lunch = 8 hours and ½ overtime
8 AM – 4 PM Half an hour lunch = 7 ½ hours 0 overtime

What they are asking for.
8 AM – 4:30 PM Half an hour lunch = 8 hours 0 overtime
8 AM – 5 PM Half an hour lunch = 8 hours 0 overtime
8 AM – 4 PM Half an hour lunch = 8 hours 0 overtime

They want any overtime from one day to fill voids where they worked fewer
hours to make up for overtime. This could be the day after or at the end of
the week when they make-up for the overtime. To add to this problem, this
has to work bi-weekly timesheet. So if someone does work over 40 hours one
week, overtime will not spill into the next weeks empty spots. Incase the
person is on vacation or sick. So on a timesheet with 14 spots for hours
worked, the top 7 can not interfere with the bottom 7, but still auto-sum at
the end.

BTW: Sorry I keep throwing things out there bit by bit, but these people
can’t seem to finish a thought.

 

[Bob Phillips]

Don,

Rather than calculate on a daily basis, is it okay just to run a weekly
total, for regular and overtime hours? That would be a lot simpler.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Might help if I posted them.

In day 7 of week 1, add

=MIN(SUMPRODUCT(ROUND((D1218-C12:C18-G12:G18)*96,0)/96),2+TIME(8,0,0))

and

=MAX(0,SUMPRODUCT(ROUND((D1218-C12:C18-G12:G18)*96,0)/96)-(2+TIME(8,0,0)))

and copy/paste to day 7 of week 2.

One question here though is the week 56 hours or 40?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)