Hi all,

I have a spreadsheet with columns B (Date), C (Time In, 24hrs), D (Time Out, 24hrs), E (Over/Under Time) and a cell that displays the total amount of overtime.

Does anyone know of a formula that will calculate over/undertime (over/under the standard 8hr day) and another that will sum column E to give a total?

From reading around i've managed to be able to display the overtime (both in time format and decimal format) but from what i gather excel doesn't handle negative time (ie: undertime), hence why you have to use decimals instead. It's the negative time that i get stuck with as if i've left work 5minutes early, the decimal displays -0.45, yet 20minutes over is displayed as 0.20 (which doesn't seem to make sense to me).

The other problem i have is that sometimes i can work a until the early hours (8am until 2am the next morning) and this obviously causes errors.

Any help will be greatly appreciated,

Dom

I have a spreadsheet with columns B (Date), C (Time In, 24hrs), D (Time Out, 24hrs), E (Over/Under Time) and a cell that displays the total amount of overtime.

Does anyone know of a formula that will calculate over/undertime (over/under the standard 8hr day) and another that will sum column E to give a total?

From reading around i've managed to be able to display the overtime (both in time format and decimal format) but from what i gather excel doesn't handle negative time (ie: undertime), hence why you have to use decimals instead. It's the negative time that i get stuck with as if i've left work 5minutes early, the decimal displays -0.45, yet 20minutes over is displayed as 0.20 (which doesn't seem to make sense to me).

The other problem i have is that sometimes i can work a until the early hours (8am until 2am the next morning) and this obviously causes errors.

Any help will be greatly appreciated,

Dom

- Double Overtime Timesheet
- Basic Future Value Formula & Interest Rate Formula
- List of Basic Excel Worksheet Formulas
- Timesheet formula for calculating ordinary hours and overtime hours.
- TIMESHEET Formulas - Separating standard hours from overtime
- Overtime on timesheet
- Help with a timesheet formula
- Formula for overtime
- Timesheet formulas
- Timesheet with running total of overtime
- Differentiating overtime and basic hours in excel
- Timesheet Formula for Hours Worked
- Timesheet formula. Adding times past midnight
- Basic "If, Then" Formula
- Timesheet formula help
- Weekly overtime - array formula possible?
- Timesheet formula please help
- Help with timesheet formulas
- HELP! OVERTIME FORMULA NEEDED
- Changing Formula References in Visual Basic
- Timesheet formulas for three in/outs, OT, Holiday, Vac, Sick, tota
- Need to create a formula to create a timesheet but haven't a clue
- Timesheet formula calculate
- Overtime Excel formula for my work policy

The formula for Time and a Half is =IF(((D13-C13)+(F13-E13))*24>8,((D13-C13)+(F13-E13))*24-8,0)

My brain is fried and I really need some assistance. Thanks in advance... Any further information needed Please post.

And if you were to need my spredsheet sample is attached.

basic interest rate formula. All I can find are annuity formulas where you

need to enter in a payment, but that is not what I need as there are no

payments.

Example 1 (Future Value): Such as on a financial calculator all I need to

enter would be the Present Value, the Interest Rate, and the # of Years to

find the future value.

Example 2 (Interest Rate): Again on a financial calculator where I would

only need to enter the Present Value, the Future Value, and the # of Years to

find what the interest rate or rate of return would be.

Thanks.

Thanks in advance

I have already got the timesheet to automatically calculate the number of hours once the start time and end time are entered(these cells are all in hh:mm format). The column which calculates the total time is AA4.

Then I have the following columns:

Wages rate: Y4

Ordinary total: AB4

Overtime total:AD4

(See attachment)

After 38 hours we need to pay at the overtime rate, so I can't just multiply the total hours by their hourly rate.

I need two formulas that will

1. Work out ordinary hours: If the number of hours is less than or equal to 38 it will calculate the hours (up to 38) by the rate, but if it is greater than 38 it will just multiply the rate by 38 (giving maximum number of ordinary hours)

2. Work out overtime hours: Where the hours exceed 38, I need a formula that will calculate how many hours are greater than 38 and multiple these additional hours by the rate by time and a half.

Can someone help me I have been stuck on this for two weeks!

Cheers

I'm a student at Tafe at the moment, and we're studying Payroll and timesheets. We have a small assignment due on Monday using excel and I cannot find a straight answer on the wide world of web. I need a couple of formulas:

Calculate standard hours (up to 8)eg., D9-C9 = 12, I need the column E to have a formula only counting the first 8 of these 12 hours.

Calculate time and a half (over 8 hrs, under 11 - time and a half is only paid first 3 hours after 8)eg., D9-C9 = 12, I need the column F to have a formula counting the first 3 hours AFTER the first 8, ie., 3 (9,10&11)Calculate double time (over 11 hrs - after the first 3 hours time and a half over time, double time is effective)eg., D9-C9 = 12, I need the column G to have a formula counting the hours AFTER the first 11.

Anyone who can help in these areas would be GREATLY appreciated!

CandicePPPclass

hoprefully this time it will stay. Anyway, I hope I can explain my problem

ok, I need a formula to put in so that I can just input the hrs. and the

time worked over or under 40 hrs. a week will come up. Also, I need one for

a running total of hrs. worked over or under 40 hrs. a week. This is an

example of what my timesheet looks like:

A= Name

B= Work period (one week)

C= hrs. worked (in one week)

D= hrs. short or over 40 hrs.

E= running total of hrs short or over 40 hrs.

Basically, the timesheet is just one long excel worksheet with each week and

the hrs. on it.

So basically, I just need a formula for columns D and E.

If this doesn't make sense and you have an idea of what to do, just let me

know and I can send you a copy of the timesheet, it's really small.

Thanks so much in advance for the help!

Im working 12 hour and longer shifts a day. Ive got my formula for C2

on-duty to D2 off-duty = E2 Total hours:min My normal working hours is 8:48. Ater 8:48 im working overtime and i am battling with the formula in F2 to show only overtime. All the timesheets formulas work on a normal shift of 8:00 hours a day. Appreciate if you could help. Thanx

a couple of formulas, but I haven't found anything that works.

Day Date Start End Lunch Total

Sick/Vacation

Monday 03-02-2005 8:00 17:00 45 8:15 0:00

Tuesday 03-03-2005 0:00 00:00 0 0:00 8:00

I would then like to add the Total column. This gives me my time for

overtime calculations. Then add the Sick/Vacation column separately.

I can do it with Start and End times, but when I try to subtract Lunch, it

stops working.

Thanks for the help.

can just input the hrs. and the time worked over or under 40 hrs. a week

will come up. Also, I need one for a running total of hrs. worked over or

under 40 hrs. a week. This is an example of what my timesheet looks like:

A= Name

B= Work period (one week)

C= hrs. worked (in one week)

D= hrs. short or over 40 hrs.

E= running total of hrs short or over 40 hrs.

Basically, the timesheet is just one long excel worksheet with each week and

the hrs. on it.

So basically, I just need a formula for columns D and E.

If this doesn't make sense and you have an idea of what to do, just let me

know and I can send you a copy of the timesheet, it's really small.

Thanks so much in advance for the help!

We have a complex payroll system at work and I'm trying to work out some payroll discrepancies, but to do this I need to differentiate between Basic and Overtime. Using the table below:

Week No. Day Date Start End Total Hours Cumulative Basic Overtime 43 Sunday 23 October 2011 00:00 00:00 00:00 00:00 43 Monday 24 October 2011 00:00 00:00 00:00 00:00 43 Tuesday 25 October 2011 14:00 22:15 07:45 07:45 43 Wednesday 26 October 2011 14:00 22:15 07:45 15:30 43 Thursday 27 October 2011 14:00 22:15 07:45 23:15 43 Friday 28 October 2011 00:00 00:00 00:00 23:15 43 Saturday 29 October 2011 00:00 00:00 00:00 23:15

The employee is contracted 18 hours per week. I need the basic column to add the hours up to 18 hours (so up to nearly half a day Thursday) and all the other hours to appear in the overtime column, like below.

Week No. Day Date Start End Total Hours Cumulative Basic Overtime 43 Sunday 23 October 2011 00:00 00:00 00:00 00:00 43 Monday 24 October 2011 00:00 00:00 00:00 00:00 43 Tuesday 25 October 2011 14:00 22:15 07:45 07:45 07:45 00:00 43 Wednesday 26 October 20110 14:00 22:15 07:45 15:30 15:30 00:00 43 Thursday 27 October 2011 14:00 22:15 07:45 23:15 18:00 05:15 43 Friday 28 October 2011 00:00 00:00 00:00 23:15 43 Saturday 29 October 2011 00:00 00:00 0 23:15

Hope this helps and if you can help back, it would be much appreciated.

Kind Regards

Chris

I need help on time formula.I need the right formula to calculate the difference between time a staff started to work on an item and the time they completed the item. I also need to calculate how much time left for the balance of the day and the average time needed to complete the balance of the gtarget item. Example is the target for the day is 22 items. If the staff started to work and completed the first item at 8.15pm ( Her shift is 8pm to 5am), then how much time is left and how much is the average time needed to com plete the balance of the 21 items for the day.I also need the formula to adjust automatically with the allocated time.Example is 8 hours on normal days and 9 hours when there is one hour overtime.

I tried several methods but could not arrive at the right solution. I have attached the spreadsheet. The columns in red is where I need the formula.

Appreciate if anyone could teach me how to input the correct formula. Thanks.

Adding the times on each separate sheet works fine but adding all the times together on sheet 8 is alll numbers. I don't want to put dates with the time in each cell, AM & PM would be fine. If anybody would like a copy of the work book and try for yourself, Ive highlighted the cells causing the problem in orange. Ive tried everything Formulas and Formating. Please Help!

My question is for help with a fairly basic formula: Here's the formula I'm trying to create: =IF(F7

basically it looks like this

time

Thurs|Fri|Sat etc

IN |7.00

OUT |

IN |

OUT |12.00

IN |12.30

IN |

OUT |

IN |

OUT |5.00

X

X has a formula =(((((AI18-((120*TRUNC((AI18/120))))))/120)+TRUNC((AI18/120))))

In AI18 is another one =ROUND((AI12+AI17),0)

AI12 has =IF(((AI11+AI9-(AI10+AI8))

I am working on a spreadsheet that calculates some wages. Here is the calculation:

UnionEmployees

Â FGHIJKL1Â Â Â Weekdays3232322Â Â Â Saturdays0003Â Â Â Sundays0004From9/22/2008Â Â MikeVictorGreg5To9/28/2008Â Reg Hrs32.0032.0032.006Limit40.00Â OT x1.5 Hrs0.000.000.007Â Â Â OT x2 Hrs0.000.000.008Â Â Â Rate $ Â Â Â 27.84 $ Â Â Â 16.70 $ Â Â Â 25.12 9Â Â Â Gross $ Â Â 890.88 $ Â Â 534.40 $ Â Â 803.84

Spreadsheet FormulasCellFormulaJ1=SUMPRODUCT(($A$19:$A$150>=$G$4)*($A$19:$A$150 Excel Jeanie HTML 4

So far, this spreadsheet has been used only to calculate weekly amount. Overtime rules are as follows:

* You must have 40 hours total for the week to get any overtime

* Saturdays are compensated at x1.5 standard rate

* Sundays are compensated at x2 standard rate

* Any weekday hours over 40 are compensated at x1.5 standard rate

* If weekday hours are below 40, Saturday hours are used towards 40 first

The following are not shown:

* Column A contains dates

* Column B contains employee names

* Column E contains total hours for the day

Now, the union wants a monthly (or a quad-weekly) report, for purposes of reconciling total union dues collected, which are based on both hours worked and amounts paid. The above will not calculate overtime correctly for a range of dates, say 9/1/2008 through 9/28/2008. This is because if, in a given week, employee had fewer than 40 hours, and in another week more than 40 hours, the legitimate overtime hours will be used towards the week with fewer than 40 hours.

I am trying to think of a workaround. It sounds like one of those things that involves a FREQUENCY formula, which is not my forte in a single-cell array. The above does work if (a) there is no overtime in the specified period, or (b) all weeks for all employees have at least 40 hours, or (c) an employee with fewer than 40 hours has no overtime in any other week.

Can anyone help? Thanks in advance.

The above works for one week.

Example below:-

JOHNAM10:3015:0010:3015:006:3011:006:3011:0010:3015:0045:005:0040:00PM18:3023:0018:3023:0018:3023:0018:3023:0018:3023:004:300:004:300:004:304:304:304:300:004:300:004:304:304:30TTL9:00OFF9:00OFF9:009:009:00

The times for each shift are added together to give a total time for each day of work as in the hotel trade its all about split shift work and the end number in orange is the total hours spent at the hotel, then the blue total is the break hours and the red is the total after the the break deduction.

What I need to do is to add a formula in the break deduction box so that every 4hours 30minutes (John) is working on any shift then a deduction of 30minutes is automatically deducted.

I hope this makes sense.

Thank you for any help given.

Marcus.

I am adding a range of cells, then in cell H22 totaling just the overtime hours, the result I am getting is 1 but should be 1.22 and if the hours are less than 40 it should be 0

current formula

=SUM((B22+D22+E22+F22+G22)>40,0)

Cell I22 is total hrs for week 1 is 41.22

Cell H22 is total hrs for week 1 should be 1.22

Thanks in advance

The first 8hrs between 8:00AM UNTIL 4:30PM are as regular normal hours.Any more than 8hrs and less than 10 hrs worked are (t1.5) overtime, Anthing above 10hrs worked have a double time loading(2.0)..

If hours worked are outside normal working hours have a double time loading(T2.0)

example

i start the first job at 5:30 am until 8:00am this equates to 2.5hrs at double time so therefore in column (T2.0) the formula should calculate 2.5hrs.

2nd job i commence straight after at 8:00am until 12:30pm equates to 4.5hrs at regular hours the formula should calculate in NORMAL HRS column 4.5hrs

3rd job starts at 12:30pm until 8:30pm equates to 4hrs at regular hours 2hrs @ time and half loading(t1.5) and 2.0 hrs at double time so therefore column NORMAL HRS = 4HRS : COLUMN T1.5 = 2HRS : COLUMN T2.0 = 2HRS.

I hope this explantion has simplified the question

CAN ANYBODY OUT THERE PROVIDE ME WITH A SOLUTION ?

list with very particular spacing and formating for use in

another program.

I'm using a looping system to work between the different

cells in the sheet which will initially just contain the

formatted data. Anyway to use visual basic to insert

formulas in these cells where the referencing system is

tied to the number of the iteration in the looping

procedure? ie, inserting the formula = sheet1(B3) in the

new sheet, where the B3 part is generated automatically in

the looping process?

Cheers

can't seem to get the formulas to work. I used the existing formula and just

added the two new columns.

=IF((((D12-C12)+(F12-E12)+(H12-G12))*24)>8,8,(((D12-C12)+(F12-E12)+(H12-G12))*24))

B C D E F G H I J K

L M N

12 Day In Out In Out In Out Reg Hrs OT Sick Holi Vac Total

13 Sunday 0.00

14 Monday 0.00

15 Tuesday 0.00

16 Wednesday 0.00

17 Thursday 0.00

18 Friday 0.00

19 Saturday 0.00

20

21 Total 0.00 0.00 0.00 0.00 0.00 0.00

Can someone help me figure out what I am doing wrong?

hours used are either time and a half between x-y hours. Completely useless

on Excel.

Can any one help me I want to make a timesheet and find a formula calculate the total days on & off also total hours per month for each employee,

Thanks all

could you pleas help me to get this overtime formula as per my work policy

weekday overtime is $ 15.00

weekend overtime per hour, minimum 4 hours $ 15.00

- the normal working hours are 9 hours start from 9:00 Am till 6:00 PM

- if the employee work in the weekend less than 4 hour he will get the cost of the full 4 hour, and if he work more than 4 hour he will get the cost of total hour

Example

Dan work in Sunday ( weekend ) 3 hour ( 4 * 15 = 60 )

Dan work in Sunday ( weekend ) 5 hour ( 5 * 15 = 75 )

Dan work in Sunday ( weekend ) 4 hour ( 4 * 15 = 60 )

thanks all for my help

No luck finding an answer? You could always try Google.