tricky formula.. please help

I have created a time roster.. easy
in each day I have start,end and break deduction.. still no probs

I need to create an output formula for hours worked after a 16:00 from
the start and end range on a day. So in the roster it will still display
normal hours, however I will create a field for hours after 16:00. this
is for calculating a different wage rate.

Thanks Aaron

Aaron,

Assuming your end times are in B1:B100, try

=SUMPRODUCT(--(B1:B100>--"16:00:00"),(B1:B100-"16:00:00"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Aaron H" <aaron@istarnetworks.com.au> wrote in message
news:41eb9d22$1@dnews.tpgi.com.au...
> I have created a time roster.. easy
> in each day I have start,end and break deduction.. still no probs
>
> I need to create an output formula for hours worked after a 16:00 from
> the start and end range on a day. So in the roster it will still display
> normal hours, however I will create a field for hours after 16:00. this
> is for calculating a different wage rate.
>
> Thanks Aaron

What are the possibilities?

Can all the work time be after 1600 (start and end time after 1600) and if
so, can the time cross midnight. Or will start time always be before 1600
and end time may sometimes be past 1600, but never past midnight.

Do you want a formula to use on each row?
--
Regards,
Tom Ogilvy

"Aaron H" <aaron@istarnetworks.com.au> wrote in message
news:41eb9d22$1@dnews.tpgi.com.au...
> I have created a time roster.. easy
> in each day I have start,end and break deduction.. still no probs
>
> I need to create an output formula for hours worked after a 16:00 from
> the start and end range on a day. So in the roster it will still display
> normal hours, however I will create a field for hours after 16:00. this
> is for calculating a different wage rate.
>
> Thanks Aaron

Tom Ogilvy wrote:
> What are the possibilities?
>
> Can all the work time be after 1600 (start and end time after 1600) and if
> so, can the time cross midnight. Or will start time always be before 1600
> and end time may sometimes be past 1600, but never past midnight.
>
> Do you want a formula to use on each row?

Can work before 16:00 (start and and after)
time won't cross midnight
Will not always start before 1600

This is to calculate a penalty rate for working wages. before 1600
wage = $10, after 16:00 $10 + 25%. Similar on Weekends, but standard
penalty through the whole day.

I have the start time in a cell and end next to it. both have a series
of selectable times.

Idea is to create the roster and from roster generate wages.


Thanks
Aaron

=if(StartTime >
TimeValue("16:00"),Mod(EndTime-StartTime,1),if(EndTime>TimeValue("16:00"),En
dTime-TimeValue("16:00"),0))

--
Regards,
Tom Ogilvy


"Aaron H" <aaron@istarnetworks.com.au> wrote in message
news:41ebdd69$1@dnews.tpgi.com.au...
> Tom Ogilvy wrote:
> > What are the possibilities?
> >
> > Can all the work time be after 1600 (start and end time after 1600)
and if
> > so, can the time cross midnight. Or will start time always be before
1600
> > and end time may sometimes be past 1600, but never past midnight.
> >
> > Do you want a formula to use on each row?
>
> Can work before 16:00 (start and and after)
> time won't cross midnight
> Will not always start before 1600
>
> This is to calculate a penalty rate for working wages. before 1600
> wage = $10, after 16:00 $10 + 25%. Similar on Weekends, but standard
> penalty through the whole day.
>
> I have the start time in a cell and end next to it. both have a series
> of selectable times.
>
> Idea is to create the roster and from roster generate wages.
>
>
> Thanks
> Aaron

format the cell with the formula as time or if you want decimal hours, then
multiply the result by 24

=if(StartTime >
TimeValue("16:00"),Mod(EndTime-StartTime,1),if(EndTime>TimeValue("16:00"),En
dTime-TimeValue("16:00"),0))*24

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OwWWZzK$EHA.2032@tk2msftngp13.phx.gbl...
> =if(StartTime >
>
TimeValue("16:00"),Mod(EndTime-StartTime,1),if(EndTime>TimeValue("16:00"),En
> dTime-TimeValue("16:00"),0))
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Aaron H" <aaron@istarnetworks.com.au> wrote in message
> news:41ebdd69$1@dnews.tpgi.com.au...
> > Tom Ogilvy wrote:
> > > What are the possibilities?
> > >
> > > Can all the work time be after 1600 (start and end time after 1600)
> and if
> > > so, can the time cross midnight. Or will start time always be before
> 1600
> > > and end time may sometimes be past 1600, but never past midnight.
> > >
> > > Do you want a formula to use on each row?
> >
> > Can work before 16:00 (start and and after)
> > time won't cross midnight
> > Will not always start before 1600
> >
> > This is to calculate a penalty rate for working wages. before 1600
> > wage = $10, after 16:00 $10 + 25%. Similar on Weekends, but standard
> > penalty through the whole day.
> >
> > I have the start time in a cell and end next to it. both have a series
> > of selectable times.
> >
> > Idea is to create the roster and from roster generate wages.
> >
> >
> > Thanks
> > Aaron
>
>