Previous Topic Next Topic

Help with Time formula calculation

Closed
Alliwin - Jul 26, 2011 at 07:33 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 2, 2011 at 09:31 AM
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



Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 28, 2011 at 04:03 PM
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
0
Alliwin
Jul 30, 2011 at 07:46 AM
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?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 30, 2011 at 04:18 PM
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
0
Alliwin
Jul 31, 2011 at 09:22 AM
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 2, 2011 at 09:31 AM
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
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response