Time functions, how do I work out number of hours

J

jeanette.rimmer

Hi,

Im trying to find a time function, formula that will let me key in start of
shift and end of shift then calculate how many hrs I should be paid.

Depending on what time of day I start, I will get paid different rates

ie between 6am and 6pm I get day rate, after 6pm I get night rate, after 8
hrs I get overtime according to what time of day Im working.

Any ideas?
 
J

JE McGimpsey

If the rate is really dependent on what time you *start*, then

A1: Start time
B1: End Time

C1: =(MOD(B1-A1,1) + OT * (MOD(B1-A1,1) - 1/3)) *
IF(ABS(A1-0.5)<=0.25, DR, NR) * 24

Where DR = Day rate
NR = Night Rate
OT = Overtime multiplier (e.g., 0.5 for time and a half)

This also assumes that your statement that the NR only applies AFTER 6
pm, not if you start AT 6 pm (i.e., 6:00 pm is DR, 6:01 pm is NR).
 
J

jeanette.rimmer

Hi, thanks for this

However I didnt really explain my problem properly

For instance, If I work from 8am to 10pm, I need to know the number of hours
worked before 6pm and the number after.

ps Im doing the calculation for more than just myself otherwise Id just use
my fingers!!

Thanks in advance


Jeanette
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top