Start time = rate.....

[christopherp]

Hello

I am trying to build a formula to automatically determine an employees
rate of pay for any given shift, which is determined by the starting
time of the shift e.g day, afternoon or night shift.

I am trying to return these values in a particular cell so I can then
multiply the rate by the number of hours worked (finish time- start
time) and therefore calculate total pay for that shift

Day shift starts at 6am, afternoon at 2pm and night shift at 6pm

I have the start times in column b formatted as custom hh:mm

I have tried entering the formula as follows

=if (b3<14:00 "$22.59", "$25.93")

Excel keeps telling me that the <14:00 part of the formula is invalid.

Can anyone tell me how I can perform this calculation based on the
starting time?

Any help is most appreciated

Chris

 

[Norman Harker]

Hi Chris!

Try one way:

=IF(B3<TIME(14,0,0),"$22.59","$25.93")

I've used the TIME function as one way of entering the time.

But as written it looks like there's only two rates for start time of
before 14:00 and after 14:00
Also, why have the rates as text? Why not use numbers formatted as
currency?

According to your written statement the formula should be something
like:

=IF(b3<TIME(14,0,0),22.59,if(B3<TIME(18,0,0),25.93,28.78))
I've put in an invented rate for the start after 18:00

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[christopherp]

Thank you very much for your help Norman you are a gentleman and a
scholar

 

[Norman Harker]

Hi Chris!

I've not heard that expression for a few years and even longer applied
to me!

Seasons Greetings

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.