Hi everyone.

I'm not very useful with Excel but I've just finished doing my staff rota and was wondering if there is an easy way to calculate the hours every one has done each week to make sure everyone has done the correct hours. I was about to start using a calculator and then realised it would take forever, lol.

I've included a zip file of the excel file and I would REALLY appreciate it if anyone could take a look at it and advise me.

Thanks very much.

I'm not very useful with Excel but I've just finished doing my staff rota and was wondering if there is an easy way to calculate the hours every one has done each week to make sure everyone has done the correct hours. I was about to start using a calculator and then realised it would take forever, lol.

I've included a zip file of the excel file and I would REALLY appreciate it if anyone could take a look at it and advise me.

Thanks very much.

- Formula to calculate the hours worked and gross amount earned
- Calculate The Hours Between The Two Dynamic Dates
- Formula to calculate hours worked
- Calculating the duration between an EndTime and the next StartTime.
- Calculate the similarity of one cell with another cell
- Calculating Man hours and days worked over multiple sheets
- Want to calculate 8 hours as one working day
- Calculate work hours based on criteria
- Calculating the Area of objects in Feet and Inches
- How do I calculate the hours spent on a project by entering a sta.
- How to calculate the hours and minutes in excel
- Calculating Rostered Hours
- Calculating Overtime Hours and Pay with two Payrates
- Calculating the minutes for a period during a time frame
- Calculating the time between one date and another
- Calculating the Area of objects in Feet and Inches
- Calculating hours in one cell
- Calculating working hours between dates including weekends
- Calculating basic hours worked, between a time range and premium hours worked
- How to calculate the mean for one set of data if its within the accepted difference?
- Calculate the difference between production & non-production time.
- How to start counting the hours in a month
- How can I archive and calculate flight hours
- Timesheet formula for calculating ordinary hours and overtime hours.

I would like some help with a formula.

I have a spreadsheet which has columns with date ranges and an amount of hours worked during that date range. I then have other columns with different date ranges. These date ranges cross over with the date ranges of the first ones. I need to find out how much is earned in the second lot of date ranges from the other information.

It's hard to explain, but if you take a look at the attachment you will understand. Under the first lot of columnns you will see that there are week and fortnightly date ranges. Then total amount of hours worked in that period. I then have a column which works out the amount of working days during that period. I then have hours worked per day, hourly rate and a gross amount for that period. I then have a different set of date ranges. I need the formula to calculate the hours worked and gross amount earned in the second lot of date ranges based on the info from the first set of date ranges. For example: fortnight 22/1/12 - 4/2/12 the person worked 53 hours at $25 per hour.This equals a gross amount earned of $1325. And for the fortnight 5/2/12 - 18/2/12 the person worked 76 hours at $25 per hour. This equals a gross amount earned of $1900. Now, the other fortnight date range shows fortnight 24/1/12 - 6/2/12. Based on the info from the first columns I need to work out what the hours and gross earnings are for the period 24/1/12 - 6/2/12.

Thankyou for all your help

How to calculate the hours between two dynamic dates.

Rules 1: It has to calculate only office hours (Let's say 9 AM to 5 PM)

Rules 2: The starting time may fall before 9 AM, then we have to do calculation from 9 AM on the same day. (Let's say start date with time 17/04/07 7:00:00 AM, then we have to consider the date with time from 17/04/07 9:00:00 AM for the calculation)

Rules 3:The starting time may fall After 5 PM, then we have to do calculation from 9 AM on the next day. (Let's say start date with time 17/04/07 8:00:00 PM, then we have to consider the date with time from 18/04/07 9:00:00 AM for the calculation)

Rules 4: It has to exclude the Saturday and Sunday

Can you please help me in this.

Thanks & Regards

Senthil

I have just created a schedule spreadsheet, does anybody know a formula that will allow me to add up the hours an employee has worked each week based on the shift times written in the cell for each day.

e.g. cell A1 is "09.00-18.00" which equals 8 hours work, cell B2 is "08.00-20.00" which equals 11 hours work.

I need a formula that will add A1 and B2 and just show the number 19.

Cheers

BankC

Currently I have the sheet set up with the following columns:

A(day of the week); B(date in mm/dd/yyyy); C(shift working, just as a reference to see if that day is a scheduled work day or an regular day off); D(StartTime); E(EndTime); F(Duration of Hours worked formatted to [hh]:mm); G(Duration of Hours OFF, also [hh]:mm).

To calculate the duration of hours worked, I'm using: =E12-D12+IF(D12>E12,1). I wrote it this way, because the end time could be after midnight (say working 17:30 to 05:30 the next day) -- This seems to work fine.

I have yet to figure out a good way to calculate the duration of hours OFF. I came up with =IF(OR(B11="",D11=""),"",(B11-B10+D11-(E10

This is a very complex problem I'm facing!! I'm a student, and I need to solve it if I want to move on with my Master's thesis!! I promise a beer (or at least the $ to get it) to the one that will save me several days of manual work!!

IN SHORT: I need to calculate the industry relatedness between the acquisitions of a specific acquiring company.

1st: I have a list of acquisitions (identifiable by a specific date, a specific acquirer's code, and a specific industry code)

2nd: for each acquisition (focal acquisition), I need to look at the other acquisitions done by the same acquirer in the 5 years prior to the focal acquisition

3rd:

- if the industry code (4-digit code) of the focal acquisition matches the industry code of one of the previous acquisitions in the last 5 years, I will give a 6

- if only the first 3 digits of the 4-digit code matches, I will give a 4

- if only the first 2 digits of the 4-digit code matches, I will give a 2

Here's a screen shot... I already started calculating but manually. I'm quite sure there should be a formula.

target-to-target-relatedness.jpg

The full set of data can be downloaded in attach: target-to-target similarity.xlsx

Example: In the screenshot you can see LINE 34 is given 4 because LINE 34 has the industry code 4925 and LINE 36 has the industry code 4922. I give 4 because (1) they share the same 3 first digits "492", (2) they are from the same acquirer, and (3) LINE 36 was in the 5 years prior than LINE 34

I would endlessly appreciate your help!

I have a file that has 8 sheets. the first seven sheets are the days of the week and the 8th sheet is where I want to calculate the hours.

The days of the week consists of "Name" "Date" Hours"

The 8th sheet consists of "Name", "Days of the week in their own column (7 columns in total)", "Total Hours" and "Total Days Worked"

Now... each day is always a different group and what I'm hoping for is to create a formula that can grab each name with their hours and throw them into the 8th sheet. So when the end of the week is done I have all the Employees weekly hours and days worked all finished.

I hope this makes sense. This file is to track about 100 employees that are on a rotational work schedule.

I have attached a sample file.

Thanks in advance for your help.

I want to calculate the total leave balance of each employee. There are various categories which need to added and 2 categories that need to subtracted to get the total leave balance.

I need to know which formula should i use so that 8 hours will be counted as one day and also i need to add hours to the days...

i am not sure if i am clear so i have attached the excel sheet for your reference...

Awaiting the reply and Thanking you all in anticipation.

Each work week is 35 hours (Mon-Fri 7 hours a day)

After 35 hours of work the first 5 overtime hours go into comp time each week.

After those 5 hours of comp time the rest of the hours worked will be in time and a half.

So for example I work Mon & Tues 9am-11pm (excluding one hour for lunch each day) that's 26 hours.

14 hours regular, 5 hours comp, 7 hours time and a half

I need a column which will calculate my 5 hours comp time and then once the five hours are summed up it then turns over into the time and a half column. Is that possible? I am very new with Excel. Any help would be appreciated!

Here is an attachment of what I've got so far:Jessica Time Sheet.xlsx

sizes and calculate the total area. The data is in Feet and Inches. If

possibe I want to be able to plug the data into excell and have it formated

so it displays 2ft 6in but is still in data form.

I have tried to do this by number formating like this #"ft" ?/12"in" but if

I typed in 2 feet and 6 inches (2.6) I get 2ft7/12in in the cell. I realize

why but I don't know how to format it in a way that excell will interpret it

as 2ft6in. Any help would be great.

After entering the height and width that way I want to calculate the area in

one cell. From what I've seen the data has to be converted to decimal form

to cacluate and then I would want the area to be put into feet and inces

again. I don't know how to do this without using many multiple cells, one to

convert it to decimal, another to multiply the area and another to put that

back as square feet.

Please Help

ending time to treack the amount of hours spent on projects. I format my

starting and ending times cells as times but I can not figure out the formula

to use to take these times and calculate the amount of time between them. If

anyone knows how to do this please let me know. You can email me at

problem is if the hours are 10.5 ( 10hour and 30 minutes). IF i calculate the

10.5 by $14 and hour it gives me 147. but if i multiply 10.30 by 14, it gives

me 144.2. I am confused i don;t know what i should do. Please help me. Thank

u in advance

http://farm3.static.flickr.com/2748/...50a38e59_o.jpg

So to calculate data intially using the formula

works fine.The links above contain images showing the examples,, it may be clearer from them the exact layout.

But since as you can see the formatting changes in first calculate there are 4 rows for the second day there are 3 rows and if its a day off one row and calculation of next day begins on following row.

Is there any way to overcome this, I need to learn how to create a macro of this for example the above is from one employee 6 month to december and there are another 70 employs to go.

My goal would be to end up with output like.

http://farm5.static.flickr.com/4057/...7794b2fe_o.jpg

ShiftTrack - Shift and Roster Management Page -1 of 1 Date Hours Bereavement 13:15 17:45 ShiftTrack - Shift and Roster Management Page -1 of 1 17:45 18:15 ShiftTrack - Shift and Roster Management Page -1 of 1 18:15 20:00 ShiftTrack - Shift and Roster Management Page -1 of 1 0:10 20:00 21:00 ShiftTrack - Shift and Roster Management Page -1 of 1 2/07/2009 7:25 Bereavement 13:15 17:45 ShiftTrack - Shift and Roster Management Page -1 of 1 3/07/2009 7:25 17:45 18:15 ShiftTrack - Shift and Roster Management Page -1 of 1 4/07/2009 7:25 18:15 21:00 ShiftTrack - Shift and Roster Management Page -1 of 1 5/07/2009 5:00 Claims A 12:15 16:00 ShiftTrack - Shift and Roster Management Page -1 of 1 6/07/2009 5:00 16:00 16:30 ShiftTrack - Shift and Roster Management Page -1 of 1 7/07/2009 7:25 16:30 20:00 ShiftTrack - Shift and Roster Management Page -1 of 1 8/07/2009 Day Off DAY OFF ShiftTrack - Shift and Roster Management Page -1 of 1 9/07/2009 Day Off Claims M 15:15 19:45 ShiftTrack - Shift and Roster Management Page -1 of 1 19:45 20:15 ShiftTrack - Shift and Roster Management Page -1 of 1 20:15 23:00 ShiftTrack - Shift and Roster Management Page -1 of 1

I currently have an Excel sheet that calculates the hours considered overtime, the pay rate, and the total regular pay and OT pay just fine. The problem I'm having is that one employee performs two funtions at my company, one as a server at 2.13/hr, and one of a managerial nature at 15.00/hr. I'm at a loss as to how to modify her entry in my excel workbook to both calculate when her overtime begins and how many hours at what pay rate to display in the appropriate cells.

For all the other employees I do this by using an IF statement that determines if their hours for the week are above 40, and if so, to display 40 in the Reg. Hours cell, and the difference in the OT Hours cell. From there calculating the amount of pay to be distributed is easy. I am just having problem figuring out how to do this same thing but while dealing with two different payrates.

I'm not sure if I attached the helppayroll.xlsx file correctly, but if not I'd be happy to find a way to post a sample online or to email it to you.

I hope I made my problem clear enough.

David

I have a start and end date and time that cross multiple days

and intervals at each hour starting at 12 midnight

I am trying to calculate the number of minutes that cross each time period during the timeframe.

So if the start date was 7/27/11 10:33 pm and the end date 7/29/11 08:45am I'd expect to see that in the Midnight-1:00Am period 120 minutes and the 8:00 AM - 9:00 am period 105 minutes

I have attached sample data.

Start Date

Start Time

End Date

End Time.

I need to calculate in hours the diffrence between the two. I am using Excel

2000 and unfortunately the cells have to remain seperate. Can anyone please

help?

sizes and calculate the total area. The data is in Feet and Inches. If

possibe I want to be able to plug the data into excell and have it formated

so it displays 2ft 6in but is still in data form.

I have tried to do this by number formating like this #"ft" ?/12"in" but if

I typed in 2 feet and 6 inches (2.6) I get 2ft7/12in in the cell. I realize

why but I don't know how to format it in a way that excell will interpret it

as 2ft6in. Any help would be great.

After entering the height and width that way I want to calculate the area in

one cell. From what I've seen the data has to be converted to decimal form

to cacluate and then I would want the area to be put into feet and inces

again. I don't know how to do this without using many multiple cells, one to

convert it to decimal, another to multiply the area and another to put that

back as square feet.

Please Help

that will calculate the hours that will be worked for the week. for example:

Name Monday Tuesday Wed Thurs Fri Sat Sun

Total

Jon 9 to 5 12 to 5 off 9 to 5 off

off 12 to 6 27

I want the spread sheet to calculate the total hrs per day and then to add

up all the days and to total it for me. Please help i am clueless. Thanks!

I am needing to calculate the hours between 2 dates (including weekend), but only counting the working hours.

I have had a go, but my fomula seems to be calculating a different answer, so somewhere along the lines it's just not right!!!

I have a start date of 29/01/2012 18:34:00 and end date of 30/01/2012 16:39:00, with working hours being Mon - Sun 08:00:00 - 17:30:00.

I manually worked this out to be 18:09:00, but as you can see on the attachment my formula works it out as 17:05:00!!!

Anyone see where its gone wrong!!! (has it subtracted the 1:04 on the start date passed teh 17:30:00 threshold!) as i don't want this to happen!!

cheers

Donna

I am trying to calculate the hours worked for a Service Engineers time sheet, my problem is we travel and work on weekends and through the night, so our basic pay is between 07:00 and 18:00, the rest is overtime, unless it is travelling time, see below for the companies explanation on this.

Site basic hours 07.00-18.00 Monday to Saturday

Premium rates 18.00-07.00 Monday to Saturday

Sundays and Bank holidays at basic rate x 180%

Travel hours Monday to Sunday basic rate x 80%

I have attached the spreadsheet so it will make more sense to you, I have filled in the Premium hours and Site Basic hours manually, these are the cells i want to automate.

I will also need to add a tick box for each day to say if it is a bank holiday or not and then include the x180%

Any thoughts on the matter will be gratefully received, if it can even be done in a formula?

Roy

I have 2 columns Item and price

A B E

1 Item price Mean

2 A 12 9.6

3 A+ 10

4 A++ 11

5 A+++ 5

6 A++++ 10

...

lets say the accepted difference is 3

i want to calculate the average price for the cells in column B for those values that they are within the accepted difference from the mean which it is 9.6 in the example above.

so, the row number five where B5 = 5 should not be in the average price ( i want to exclude this value)

Thanks

I am preparing the production report of all employees. I need the Excel worksheet to calculate the non-production time automatically for each employee from the standard 8hr time.

Eg: 1. Accountingwork :12:00 Pm - 3:00 Pm = 3Hrs - Production time.

2. Tax Work : : 3:00 Pm - 5:00 Pm = 2Hrs - Production time.

3. Others (non-production) : = 3Hrs -

I need help on the :

1. To flow balance of 3 hrs (8-3-2) automatically on the excel (not sure about the formula).

2. The total working hours of all employees in the excel. ( Productions & non-production).

Hope, I have not confused you. This would be of great help for me to prepare the report in less time and accurately

Regards

I am using a spreadsheet to calculate the hours in each month. I would like for it to start counting each month in 24 hr increments as each month begins and then stops counting after the last day of the month and then in the next months cell start counting again.

For instance,

A1 thru A12 are the months in the year,

B1 thru B12 are the hours accumulated in each cell for each month.

For the months in the future I need them to = 0

For the months in the past I need them to equal the total hours for that month.

I am currently using the following:

HTML Code:

this references the current date.

The problem with this is I have to wait for the beginning of each mont to enter the formula in order for it to work.

As always you guys are the best resource for help concerning Excel.

Thanks for any and all help

Kenny

And then I want excel to calculate the hours flown in the last 30 days and the last 3 months.

I hope I am clear in my explanation.

Can anyone help me with this?

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

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