Timesheet w/ Multiple Shift PayRate & OT Question 1

[mrteacher]

I am putting together a workbook where IN / OUT is entered and it calculates Total Hrs and OT Hrs once 40 Hrs has been reached in the week.

Problem:
1) Each "shift" is at least 12 hrs long
2) Some "shifts" span two or more manufacturing shifts
3) Once Hrs is greater than 40 in the week the remaining must be charged as OT -- even if 6 hrs into a shift everything after must be OT and the following days.
4) I am thinking that "IF STATEMENTS" are not the right way to handle this task.
5) The next worksheet will have a database of totals of all the employees to print out for sending to AP each week. This should not be too difficult to do though.

I tried to copy and paste in the "table" but do not think it pastes very well.

Here is a break down of the shift times:
5AM to 5PM -- this covers 3rd, 1st, 2nd shifts
5PM to 5AM -- this covers 2nd and 3rd shifts

Employee John Doe
Scheduled Time Regular Time Over Time
Date In Out Total Hrs Reg Hrs OT 1st Shift 2nd Shift 3rd Shift 1st Shift 2nd Shift 3rd Shift Total
Sunday 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Monday 5:00 PM 5:00 AM 12.00 12.00 0.00 0.00 8.00 4.00 0.00 0.00 0.00 12.00
Tuesday 4:45 PM 5:15 AM 12.50 24.50 0.00 0.00 6.25 6.25 0.00 0.00 0.00 12.50
Wednesday 0.00 24.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Thursday 5:00 AM 5:00 PM 12.00 36.50 0.00 8.00 2.00 2.00 0.00 0.00 0.00 12.00
Friday 5:00 AM 5:00 PM 12.00 40.00 8.50 4.00 0.00 2.00 4.00 2.00 0.00 12.00
Saturday 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Total 48.50 40.00 8.50 12.00 16.25 14.25 4.00 2.00 0.00 48.50

The following formulas work fine...
Total Hrs formula is: =IF(COUNT(B6:C6)=2,MOD(C6-B6,1)*24,0)
Reg Hrs formula is: =IF((D6=0)*AND(E5=40),0,IF((E5+D6)>40.01,40,E5+D6))
OT Hrs formula is: =IF((E6=40)*AND(D6<>0),(E5+D6)-40,0)

Notice that once Reg Hrs = 40 for Friday that then 8.5 hrs is alloted into OT. And that Hrs in OT shifts needs to populate.

Right now ALL the Reg & OT shift hrs is not populating by formula (only by hand) and I need them to populate by formula.

**What is a streamlined formula to make the last 6 columns before the Total Column populate correctly?**

I am not sure if zip files can be uploaded for you to see the workbook???

Thanks for your help - Steve

 

[mrteacher]

A few more things for your info...

You can go to

http://www.excelforum.com/showthread.php?p=1826279#post1826279

to see a thread on this.

The attachment listed as Timesheet Trial912a.zip posted on 9/14/2007 is the lasted I have for this file.

Many thanks a Lost Novice...

Steve

 

[SkipVought]

"I tried to copy and paste in the "table" but do not think it pastes very well."

Check out the [blue]Process TGML[/blue] link below the Posting Window. Your data could look like this...
[tt]
Employee John Doe
Scheduled Time Regular Time Over Time
Date In Out Total Hrs Reg Hrs OT 1st Shi 2nd Shi 3rd Shi 1st Shi 2nd Shi 3rd Shi Total
Sunday 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Monday 5:00 PM 5:00 AM 12.00 12.00 0.00 0.00 8.00 4.00 0.00 0.00 0.00 12.00
Tuesday 4:45 PM 5:15 AM 12.50 24.50 0.00 0.00 6.25 6.25 0.00 0.00 0.00 12.50
Wednesday 0.00 24.50 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Thursday 5:00 AM 5:00 PM 12.00 36.50 0.00 8.00 2.00 2.00 0.00 0.00 0.00 12.00
Friday 5:00 AM 5:00 PM 12.00 40.00 8.50 4.00 0.00 2.00 4.00 2.00 0.00 12.00
Saturday 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Total 48.50 40.00 8.50 12.00 16.25 14.25 4.00 2.00 0.00 48.50
[/tt]
You have a problem in your head that you have not clearly defined. You have merely posted several statements, but where is the question?

Please be CLEAR, CONCISE, COMPLETE & SPECIFIC.


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[firefytr]

Check out Office online. There are many templates for this.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[mrteacher]

Hi Skip...

I thought I was being very concise with what I am TRYING to accomplish yet.

Over in the

Reg Hrs OT Hrs
1st Shift 2nd Shift 3rd Shift 1st Shift 2nd Shift 3rd Shift

Columns I need to have those fields populate based on the information from "In" and "Out" and whether Hrs = 40 or above -- if above then populate ONLY those Hrs of OT for the "shift" time of the 12+ hr shift.

In the above example, notice that I manually filled in the --

Reg Hrs OT Hrs
1st Shift 2nd Shift 3rd Shift 1st Shift 2nd Shift 3rd Shift

-- columns to represent what one week might look like.

That "representation" is what I am trying to figure out formulas for.

I know that what I have going for an example should be feasible -- but am not at all sure how to go about it. I am very lost in this matter.

Thanks - Steve

 

[mrteacher]

Hi Zack;

I realize you mean well - however, I have looked through all the "templates" and none of them really seem to match the complicated nature of this scenario. With not only a broad spectrum of differing shift and shift rates with the shifts this is more complicated than any template.

Steve

 

[SkipVought]

First of all, you need to fully understand Date/Time values.

By this example, it appears that you don't...
[tt]
Monday 5:00 PM 5:00 AM
[/tt]
Your data should be like this
[tt]
DateTime IN DateTimeOUT
2007/09/10 17:00 2007/09/11 05:00
[/tt]
notice that the DateTime is IN on one date and OUT on the next.

You can FORMAT the DateTime NUMBER in almost ANY format you'ld like.

Check out faq68-5827

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[mrteacher]

Hi Skip -

It may appear that I do not have IN and OUT as separate cells but they are. So for that, I do understand the time/date thing.

I was not sure "if" I needed to have the full month,day,year,time inserted. I was thinking that if you have 5P and ending is 5A then it obviously has to be the next day and to have the formula look at that as such.

::Monday 5:00 PM 5:00 AM
::Your data should be like this
:ateTime IN DateTimeOUT
::2007/09/10 17:00 2007/09/11 05:00
::notice that the DateTime is IN on one date and OUT on the next.

Would it be more beneficial to have 4 input cells as follows:
Date IN Time IN Date OUT TIME OUT
9/10/2007 17:00 9/11/2007 05:00

This way, would the separation of field make some type of "IF" statement work better?

There are two things that this workbook in the end will do - minimize data input (time consumption) and have zero errors to Accounts Payable.

Steve

 

[SkipVought]

This issue is NOT separate cells. It is that In and OUT must ALSO contain the Date part as well.

Otherwise the OUT value is LESS THAN the IN value.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[mrteacher]

Hi Skip,

OK - if the IN/OUT must have DATE value in there would I be right believe that a complicated "IF" statement will work to give me the necessary results in the cells to the right?

I am not even sure of the proper place to start a formula for this...

In researching this on the NET - I have not come any closer than when I started this project... BUT as we move forward, I am hoping to learn.

Steve

 

[SkipVought]

To start...
[tt]
Total Lapsed HOURS: (OUT - IN)/24
[/tt]

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[mrteacher]

Hi Skip -

I already have for the first three columns:
Total Hrs = IF(COUNT(B6:C6)=2,MOD(C6-B6,1)*24,0)
Reg Hours = IF((D6=0)*AND(E5=40),0,IF((E5+D6)>40.01,40,E5+D6))
Total Reg Hrs = IF(MAX(E5:E11)>40.01,40,MAX(E5:E11))
OT Hours = IF((E6=40)*AND(D6<>0),(E5+D6)-40,0)

What I am looking for now is:
Regular Time
1st Shift 2nd Shift 3rd Shift
0.00 0.00 0.00

Over Time
1st Shift 2nd Shift 3rd Shift
0.00 0.00 0.00


-- for the input to calculate based on start time which shift gets the hours based on this lookup table (Shift_DB):
Shift In Out
1 07:00 16:59
2 17:00 22:59
3 23:00 06:59

I am not sure if this makes any sense - I know that I am missing something in this formula I am starting:

[red](Looking for 1st Reg Shift Hrs)
=IF(OT_Hrs>0,IF(MOD(Ttl_Hrs-OT_Hrs,1)*AND IN=>07:00 AND <=16:59,(8-Ttl_Hrs),0))

(Looking for 1st OT Shift Hrs)
=IF(OT_Hrs>0,IF(IN=>07:00 AND =<16:59,(8-OT_Hrs),0))
[/red]
Am I on the right track here - because I get error coming up when using these formulas? I need assistance if you can give me a few pointers here...

Steve

 

[SkipVought]

Check your AND function. The form is
[tt]
AND(expression1,expression2,......)
[/tt]
NOT
[tt]
expression1 AND expression2 AND.....
[/tt]


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

And you STILL do not account for the DATE in the IN and OUT values!!!

Your Shift Table ought to be...
[tt]
BaseDate: 2007/09/10
Shift In Out
1 BaseDate 07:00 BaseDate 16:59
2 BaseDate 17:00 BaseDate 22:59
3 BaseDate 23:00 BaseDate+1 06:59

[/tt]


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

You need to have DATES in your IN/OUT values and in column A

I have Sunday set up as 9/8/2007. This is the data FORMATTED....
[tt]
Date In Out
Sunday
Monday Mon 17:00 Tue 05:00
Tuesday Tue 16:45 Wed 05:15
Wednesday
Thursday Thu 05:00 Thu 17:00
Friday Fri 05:00 Fri 17:00
Saturday
[/tt]
Formatted GENERAL....
[tt]
Date IN Out
39334
39335 39335.70833 39336.20833
39336 39336.69792 39337.21875
39337
39338 39338.20833 39338.70833
39339 39339.20833 39339.70833
39340
[/tt]
Here's Regular Time EXAMPLE for MONDAY (ROW 5)
[tt]
1 2 3
1st Shi 2nd Shi 3rd Shi

=AND($B5-$A5>=VLOOKUP(G$2,$A$19:$C$21,2,FALSE),$B5-$A5<=VLOOKUP(G$2,$A$19:$C$21,3,FALSE))

[/tt]
$A$19:$C$21 is the Shift Table.

This formula is an EXAMPLE using the IN Date/Time value. It returns TRUE/FALSE indicating where the IN value falls in shifts. Need to combine with the OUT value. That's your job.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[mrteacher]

[red]Thanks[/red] SKIP!!!

I am going to have to "think" and review what you have written here!!

I think once I "see" in a step process this working I will be able to get the last part -- at least I hope so.

The worst part of this whole thing is that yrs ago I was much better at Excel -- when you are not using that process for long time you get very "fuzzy".

Steve

 

[SkipVought]

Build your expressions one step at a time. Then start putting the pieces together to get what you need.

Post back as needed.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[mrteacher]

Hey Skip -

Sorry I had not given you any stars before now... forgot totally about that. TOO much multi-tasking.

Steve

 

[SkipVought]

Thanx!

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]