Calculate rate of Pay

[Ntisch]

Hope someone can assist.

Looking for a formula that will calculate a different rate of pay based on time employee starts and finishes work.

Two time rates of pay;

8am/8pm - rate of pay 1
8pm/8am - rate of pay 2

I have attached a screen shot of the spreadsheet.

Effectively I just need a formula to look at times in the START and END columns and calculate the appropriate formulas in the RATE 1 and RATE 2 columns.

Thanks in advance.

[jmancha]

Is your Rate 1 and Rate 2 coming from a drop down? One thought would be that you use an If Function such as:

=IF(B5="Rate 1","5.00",IF(B5="Rate 2","7.25",""))*A5

A5 being the number of hours and B5 being the rate type. Using 5.00 for Rate 1 and 7.25 for rate 2

The reason I ask if you are using a drop down is b/c the only way this will work accurately, the Rate 1 and Rate 2 will need to be typed exactly the same each time.

Also, if there is no data under the rate column, this would leave that cell blank.

[Ntisch]

Hi - thanks for your response.

I am getting the rates from a drop down menu - using Vlookup.

What I'm hoping to do is construct a forumula that looks at both the start and end times, and calculates the amount of hours for Rate 1 in one column (Rate 1 $) and calculates the amount of hours for Rate 2 in another column (Rate 2 $).

Thanks

[arthurbr]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Ntisch]

Hi - I have attached a dummy workbook.

The columns I'm interested in are J and K. These are the ones that I want to search the start (D) and end (G) time columns and return a total of how many hours there are of each.

Hope this makes sense.

[arthurbr]

Can the employee begin at any time and work any number of hours ?

[Ntisch]

Hi Arthur - they can start at any time and work any numbers of hours.

No-one would work more than 24 hours straight, though.