Help with Time formula calculation

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello, I am currently developing a spreadsheet (Excel2003) to calculate overtime hrs in a small business. I have a start time, End time. I have broken it down into two columns showing a calc for hrs & min. I have now included an additional column to use as a variable for my o/t rate (Norm, 1,5 , 2,0 hrs) All of these work perfectly but am wanting to enter a "PH" code which will automatically indicate the holiday hrs as well as multiply the hrs by two (Double Time) Can someone assist with a formula to do this please?
Many thanks



1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
let say
A1: Start time
B1: End time
C1: Code (Norm, 1.5 , 2,0 , PH) can be upto 7
D1: =IF(OR(A1="",B1=""),0,B1-A1)*IF(C1="PH",2,IF(C1="Norm", 1,C1))
Basically saying if
The first IF says
A1 or b1 is blank then show 0 in d1 else get the difference

then use other if to get the "multiplier", in this sample I am saying if c1 is PH then multiplier is 2, if it is norm, then it is 1 and default is that use what ever is in C1
Thanks have tried your formula and is working. Now also need to know how to calcualte hhmm greater than 24hrs. I have formatted to [h]mmm and gives me hrs greater than 24 but is doing something strange when trying to calculate a total hrs by a rate of pay. Can you advise?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What you mean by hours greater than 24. What exactly in that cell ? is it data and time formatted at hh:nn or some thing else
Basic outlay of calculation is:
col A = Day of week
col B = Start time
col C = End time
col D= Hrs worked
col E= Rate (Norm, 1,5, 2, ph)
Below this data have a summary of hrs as per o/t rate (Norm, 1.5, 2, ph) this total needs to be multiplied by pay rate e.g. ZAR10.5
Bottom of col D = Total hrs o/t worked for week (Used for data - formatted [h]mmm)
Currently showing a figure of 53:15 (hrs) and when multiplied by 10.5 giving a total of 23??? when total calculated manually reads 558.08.
hope this is a little clearer. Is there anyway of mailing you a Pdf screenshot of the file to see my layout?
Many thanks.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You can upload a sample book with some sample data at some shared site like google etc and post back the link to the file here