Time functions, how do I work out number of hours
 

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?

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).

In article ,
"jeanette.rimmer" wrote:

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.

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


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

In article ,
"jeanette.rimmer" wrote:

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.