(Msg. 1) Posted: Tue Aug 05, 2008 10:51 pm
Post subject: Shift Differential TimeSheet Formulas Add to elertz Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
I just need a formula, or formulas that will calculate my shift Differential
Time from 8pm-8am. So if you work any hours between 8pm-8am(the next day)
those hours apply to shift diff. Also i need another column that calculates
the OT, (over 8 hours) ;and also one column that calculates any hours worked
over 8 hours that are past 8pm; also another that calculates double time (any
time worked over 12 hours)
Shift Diff + Hours = 8 hours always
So the header should read:
Start, End, Shift Diff Hours, Hours work, OT hours, Shift Diff OT, Double Time
Example 1:
So if I worked: 0600 - 2100 on 8-5-08
I would have 2 hours of shift diff, 6 hours of regular, 4 hours of OT, 1
hour of Double Time
(There is no such thing as Shift Diff Double time Any time worked over 12
hours is always DT)
Example 2:
0800 - 2100
8 hours of Regular hours, 4 hours of OT, 1 Hour of Shift Diff OT.
If anyone could help Please Please let me know. i'm not proficient in Excel
to create something like this.
Thanks!
(Msg. 2) Posted: Wed Aug 06, 2008 9:44 am
Post subject: Re: Shift Differential TimeSheet Formulas Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Sophia wrote:
> I just need a formula, or formulas that will calculate my shift Differential
> Time from 8pm-8am. So if you work any hours between 8pm-8am(the next day)
> those hours apply to shift diff. Also i need another column that calculates
> the OT, (over 8 hours) ;and also one column that calculates any hours worked
> over 8 hours that are past 8pm; also another that calculates double time (any
> time worked over 12 hours)
>
> Shift Diff + Hours = 8 hours always
>
> So the header should read:
> Start, End, Shift Diff Hours, Hours work, OT hours, Shift Diff OT, Double Time
>
>
> Example 1:
> So if I worked: 0600 - 2100 on 8-5-08
>
> I would have 2 hours of shift diff, 6 hours of regular, 4 hours of OT, 1
> hour of Double Time
>
> (There is no such thing as Shift Diff Double time Any time worked over 12
> hours is always DT)
>
> Example 2:
> 0800 - 2100
>
> 8 hours of Regular hours, 4 hours of OT, 1 Hour of Shift Diff OT.
>
> If anyone could help Please Please let me know. i'm not proficient in Excel
> to create something like this.
> Thanks!
>
I think this is what you are looking for:
A1:H1 - Headers
Start, End, Shift Diff Hours, Hours Worked, OT Hours, Shift Diff OT, Double
Time, Regular
A2 = start time entered as time (6:00)
B2 = end time entered as time (21:00)
C2 = IF(B2<A2,(1+B2-TIME(20,0,0)),MAX(0,B2-TIME(20,0,0)))*24
D2 = IF(B2<A2,(1+B2-A2)*24,(B2-A2)*24)
E2 = MAX(D2-G2-F2-8,0)
F2 = MAX(C2-G2,0)
G2 = IF(D2>12,D2-12,0)
H2 = D2-G2-F2-E2
This give different results than what you stated in your first example above,
but I believe they are right per your description of what you want.
(Msg. 3) Posted: Wed Aug 06, 2008 10:54 am
Post subject: Re: Shift Differential TimeSheet Formulas Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Glenn wrote:
> Sophia wrote:
>> I just need a formula, or formulas that will calculate my shift
>> Differential Time from 8pm-8am. So if you work any hours between
>> 8pm-8am(the next day) those hours apply to shift diff. Also i need
>> another column that calculates the OT, (over 8 hours) ;and also one
>> column that calculates any hours worked over 8 hours that are past
>> 8pm; also another that calculates double time (any time worked over 12
>> hours)
>> Shift Diff + Hours = 8 hours always
>>
>> So the header should read:
>> Start, End, Shift Diff Hours, Hours work, OT hours, Shift Diff OT,
>> Double Time
>>
>>
>> Example 1:
>> So if I worked: 0600 - 2100 on 8-5-08
>>
>> I would have 2 hours of shift diff, 6 hours of regular, 4 hours of OT,
>> 1 hour of Double Time
>>
>> (There is no such thing as Shift Diff Double time Any time worked over
>> 12 hours is always DT)
>>
>> Example 2:
>> 0800 - 2100
>>
>> 8 hours of Regular hours, 4 hours of OT, 1 Hour of Shift Diff OT.
>> If anyone could help Please Please let me know. i'm not proficient in
>> Excel to create something like this. Thanks!
>
>
> I think this is what you are looking for:
>
> A1:H1 - Headers
>
> Start, End, Shift Diff Hours, Hours Worked, OT Hours, Shift Diff OT,
> Double Time, Regular
>
> A2 = start time entered as time (6:00)
> B2 = end time entered as time (21:00)
> C2 = IF(B2<A2,(1+B2-TIME(20,0,0)),MAX(0,B2-TIME(20,0,0)))*24
> D2 = IF(B2<A2,(1+B2-A2)*24,(B2-A2)*24)
> E2 = MAX(D2-G2-F2-8,0)
> F2 = MAX(C2-G2,0)
> G2 = IF(D2>12,D2-12,0)
> H2 = D2-G2-F2-E2
>
>
> This give different results than what you stated in your first example
> above, but I believe they are right per your description of what you want.
Regarding formatting, A2:B2 should be formatted as time (h:mm), C2:H2 should be
formatted as numbers per your requirements (I used 0.00).
(Msg. 4) Posted: Wed Aug 06, 2008 5:43 pm
Post subject: Re: Shift Differential TimeSheet Formulas Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
It the most part it work! Thanks But the only thing is the Shift Diff column.
It works if i put in anything before 6am, but if i try to put 7am, that
should equal 1 hour of shift diff, but the colomn comes up as 0.
"Glenn" wrote:
> Sophia wrote:
> > I just need a formula, or formulas that will calculate my shift Differential
> > Time from 8pm-8am. So if you work any hours between 8pm-8am(the next day)
> > those hours apply to shift diff. Also i need another column that calculates
> > the OT, (over 8 hours) ;and also one column that calculates any hours worked
> > over 8 hours that are past 8pm; also another that calculates double time (any
> > time worked over 12 hours)
> >
> > Shift Diff + Hours = 8 hours always
> >
> > So the header should read:
> > Start, End, Shift Diff Hours, Hours work, OT hours, Shift Diff OT, Double Time
> >
> >
> > Example 1:
> > So if I worked: 0600 - 2100 on 8-5-08
> >
> > I would have 2 hours of shift diff, 6 hours of regular, 4 hours of OT, 1
> > hour of Double Time
> >
> > (There is no such thing as Shift Diff Double time Any time worked over 12
> > hours is always DT)
> >
> > Example 2:
> > 0800 - 2100
> >
> > 8 hours of Regular hours, 4 hours of OT, 1 Hour of Shift Diff OT.
> >
> > If anyone could help Please Please let me know. i'm not proficient in Excel
> > to create something like this.
> > Thanks!
> >
>
>
> I think this is what you are looking for:
>
> A1:H1 - Headers
>
> Start, End, Shift Diff Hours, Hours Worked, OT Hours, Shift Diff OT, Double
> Time, Regular
>
> A2 = start time entered as time (6:00)
> B2 = end time entered as time (21:00)
> C2 = IF(B2<A2,(1+B2-TIME(20,0,0)),MAX(0,B2-TIME(20,0,0)))*24
> D2 = IF(B2<A2,(1+B2-A2)*24,(B2-A2)*24)
> E2 = MAX(D2-G2-F2-8,0)
> F2 = MAX(C2-G2,0)
> G2 = IF(D2>12,D2-12,0)
> H2 = D2-G2-F2-E2
>
>
> This give different results than what you stated in your first example above,
> but I believe they are right per your description of what you want.
>
(Msg. 5) Posted: Fri Aug 08, 2008 9:18 am
Post subject: Re: Shift Differential TimeSheet Formulas Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
>>> Sophia wrote:
>>>
>>> I just need a formula, or formulas that will calculate my shift Differential
>>> Time from 8pm-8am. So if you work any hours between 8pm-8am(the next day)
>>> those hours apply to shift diff. Also i need another column that calculates
>>> the OT, (over 8 hours) ;and also one column that calculates any hours worked
>>> over 8 hours that are past 8pm; also another that calculates double time (any
>>> time worked over 12 hours)
>>>
>>> Shift Diff + Hours = 8 hours always
>>>
>>> So the header should read:
>>> Start, End, Shift Diff Hours, Hours work, OT hours, Shift Diff OT, Double Time
>>>
>>>
>>> Example 1:
>>> So if I worked: 0600 - 2100 on 8-5-08
>>>
>>> I would have 2 hours of shift diff, 6 hours of regular, 4 hours of OT, 1
>>> hour of Double Time
>>>
>>> (There is no such thing as Shift Diff Double time Any time worked over 12
>>> hours is always DT)
>>>
>>> Example 2:
>>> 0800 - 2100
>>>
>>> 8 hours of Regular hours, 4 hours of OT, 1 Hour of Shift Diff OT.
>>>
>>> If anyone could help Please Please let me know. i'm not proficient in Excel
>>> to create something like this.
>>> Thanks!
>>>
>>
>> "Glenn" wrote:
>>
>> I think this is what you are looking for:
>>
>> A1:H1 - Headers
>>
>> Start, End, Shift Diff Hours, Hours Worked, OT Hours, Shift Diff OT, Double
>> Time, Regular
>>
>> A2 = start time entered as time (6:00)
>> B2 = end time entered as time (21:00)
>> C2 = IF(B2<A2,(1+B2-TIME(20,0,0)),MAX(0,B2-TIME(20,0,0)))*24
>> D2 = IF(B2<A2,(1+B2-A2)*24,(B2-A2)*24)
>> E2 = MAX(D2-G2-F2-8,0)
>> F2 = MAX(C2-G2,0)
>> G2 = IF(D2>12,D2-12,0)
>> H2 = D2-G2-F2-E2
>>
>>
>> This give different results than what you stated in your first example above,
>> but I believe they are right per your description of what you want.
>>
>
> Sophia wrote:
> It the most part it work! Thanks But the only thing is the Shift Diff column.
> It works if i put in anything before 6am, but if i try to put 7am, that
> should equal 1 hour of shift diff, but the colomn comes up as 0.
Should now count all hours between 8pm and 8am, even at the beginning of the
shift, which doesn't exactly reflect your original request but appears to be
what you want.
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in/Register/Password
Office