Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Basic Overtime/Timesheet Formula

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


Post your answer or comment

comments powered by Disqus
I currently have two columns on my timesheet. The columns are Regular and Overtime. I'm currently trying to introduce a third column which is Double Overtime. The formula for regular is =IF((((D13-C13)+(F13-E13))*24)>8,8,((D13-C13)+(F13-E13))*24)
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.

I need to figure out how to do two functions, the basic future value and the
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.

Can any body will help me get list of basic work sheet formulas required in day to day work. I am a beginner.

Thanks in advance

Hi I am building a timesheet and I have become a bit stuck.

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

Oh please help!!

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

I've posted this problem twice already and the next day my post disapears, so
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!

I am trying to create a timesheet for my job, my only problem left to solve is to get the Total hours column to include the travel hours...and not affect the 2 columns labeled "regular" and "overtime" as we are paid travel separate but I need to include it in the total......any help would be appreciated, on the attachment there is the formula I used to achieve the regular and overtime separation. Thank You

Hi

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

I would like to set up a timesheet with the format listed below. I've tried
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.

Hi, 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!

Hi Guys n Girls

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

Hi All,

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.

This is my problem, I have created a timesheet that has 8 sheets, sheet 1 to 7 is the days of the week containing start and finish times that go over midnight, sheet 8 is a weeks summary that calculates the total time worked.
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

Ok this one is beyond me, I am trying to redo a sheet one of my colleges did, I can't get in touch with them as I don't have their details anymore anyway... what i am trying to do is change some of the current sheet formulas to return a time value as a decimal rather than an actual time i.e. I want 7.5 and not 7.3 these cells are all set up as numbers (formatting)

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))

Hi,

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.

Hi, I need to enter a formula to my work timesheet so that it automatically deducts break hours.

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.

Hi

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

I require a formula to calculate overtime,The formula needs to be driven by the time of day worked and amoutnt of hours worked between a specific period.

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 ?

I have matrix (221 by 221) that I need to convert into a
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

I downloaded a timesheet like the one below. I added the extra in/out and
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?

Timesheet Formula: I am trying to create a formula to calculate whether the
hours used are either time and a half between x-y hours. Completely useless
on Excel.

Hi all,

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

Hello friend,

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.