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

tricky formula.. please help
 

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

tricky formula.. please help
 

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

tricky formula.. please help
 

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

tricky formula.. please help
 

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

--
Regards,
Tom Ogilvy


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

tricky formula.. please help
 

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(EndTimeTimeValue("16:00"),En
dTime-TimeValue("16:00"),0))*24

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
=if(StartTime

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

--
Regards,
Tom Ogilvy


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

tricky formula.. please help
 

Im trying to create a matrix that will look at my schedule and tell m
how many employees I have on at any given time of day. However th
following will not add in people at all if they have a shift that span
past midnight. Does anyone know how I can accomidate this or tweak th
function below???:confused:

=SUMPRODUCT((--$C$14:$C$19<$F1)*(--$D$14:$D$19$F1))


The above formula is in Cell G1 and copy down to G13.
Foramt cells as General.

Below are the content of cells F1, F2, F3.... F13

9:30 AM
10:00 AM
10:30 AM
11:00 AM
11:30 AM
12:00 PM
12:30 PM
1:00 PM
1:30 PM
2:00 PM
2:30 PM
3:00 PM
3:30 PM


A B C D
13 Name Time In Time Out
14 Paul 8:00 AM 3:50 PM
15 Mary 9:30 AM 2:30 PM
16 Peter 8:15 AM 4:30 PM
17 Tom 9:15 AM 3:15 PM
18 Alice 11:15 AM 2:15 PM
19 Jane 1:45 PM 5:00 P

--
clinton.holde
-----------------------------------------------------------------------
clinton.holder's Profile: http://www.excelforum.com/member.php...fo&userid=3042
View this thread: http://www.excelforum.com/showthread.php?threadid=33613