Forum Discussion

MASB70's avatar
MASB70
Copper Contributor
Apr 17, 2022

Function that calculates salaries depending on the IN and OUT time

Hi everyone,

I am looking for a dynamic formula that can calculate work schedules. Depending on the schedules, the wage value changes. Example

Criteria

From 19:00 to 1:00 am, the wage is 2,000

From 1:00 to 4:00 the wage is 2,500

The function that I was using is: =24*MOD(TIME(1,0,0)-B1,1)*2,000+24*(C1-TIME(1,0,0))*2500

That I see using this function is when the person doesn't work passing the 12:00 am the function fails.

Do you know how to resolve this issue?

I will appreciate your help.

Thank you

     A             B           C.          D

1  Tina       22:30     4:00     12,500

2. Julia       19:00.     1:00.    12,000

3  Rob.       19:00.     23:30.   ?

 

 

 

Formulas and Functions

4 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Apr 17, 2022

    MASB70 

     

    Before we deal with the formula, I'd want to understand the table. It seems that there's a big gap in the rate schedule: what rate applies between 4:00 and 19:00?

     

    And why the question mark on that one row? Isn't the answer simple?

    Rob.       19:00.     23:30.   ?

    That looks like 4.5 hours at 2,000. i.e., 9,000  Right?

     

     

    • MASB70's avatar
      MASB70
      Copper Contributor
      Apr 17, 2022
      Mathetes,
      Thank you for your interesting in my problem.
      The schedule is at nighttime.
      Then the rate is from 19:00 to 1:00 the wage is 2,000 per hour
      From 1:00 until 4:00 in the morning the wage is 2,500 per hour
      In the case of my example Rob, yes the wage will be 9,000, however the formula that I'm using now has problems when the worked time doesn't pass 24:00
      I hope this explanation can help you.
      Thank you

Resources