Previous Topic Next Topic

Help with Time formula calculation

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

Réponse 1 / 1
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
28 Jul 2011 à 16:03
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
Alliwin
30 Jul 2011 à 07:46
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?
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
30 Jul 2011 à 16:18
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
Alliwin
31 Jul 2011 à 09:22
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.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
2 Aug 2011 à 09:31
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

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
Connection keeps timing out
Kimber -
Jeff -

29 responses