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

Help with Time formula calculation
Vat calculation excel - Guide
Popcorn time download - Download - Movies, series and TV
Calculation is incomplete. recalculate before saving - Guide
Facebook id verification time - Guide
Logitech formula vibration feedback wheel driver - Download - Drivers

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

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?

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

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.

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

Similar discussions

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

The system time is invalid

Similar discussions

Subscribe To Our Newsletter!