Free Microsoft Excel 2013 Quick Reference

Rota - calculate the hours every one has done each week

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.


Post your answer or comment

comments powered by Disqus
Hi,

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

Hi Guys,

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

Hi There,

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

I'm trying to make a worksheet that verifies the number of hours an employee works AND the number of hours off the employee has between work shifts.

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

Hi guys!!

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'm trying to come up with a solution to calculate the man hours and days worked over multiple sheets.

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.

Dear Friends,

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.

Hi I'm working on a bi-weekly time sheet that goes as follows:

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

I am completing a project in which I need to take data collected on window
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

I am trying to make a spread sheet that I can enter a starting time and and
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

I am trying to calculate the total hours and minutes by weelky in excel. The
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

I have what is essentially a simple problem, I want to calculate employee rostered hours or days off from an exported crystal reports. The problem isn't how to calculate the hours but to calculate accurately when formatting changes occur in the exported report. This is an example for the exported sheet data.

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

So to calculate data intially using the formula
works fine.

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
The links above contain images showing the examples,, it may be clearer from them the exact layout.

I'm not sure I'll explain this well, but I'll try my best:

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

Thanks in advance for any help I've been struggling with this one

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.

Hi there. I have a worksheet with four cells in it:

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?

I am completing a project in which I need to take data collected on window
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

i am new to excel, i am trying to make a spread sheet for a weekly schedule
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!

Hi All,

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

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

Dear,

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

Hi

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

Hello All.
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

I am a flight attendant and I would like to create a excel file with my flight hours. But I don't want to log the exact hours but the average f.e. if a flight to New York is 5 1/2 hours, I just want to write NY and then Excel changes that to 5 1/2 hours. Is this possible in Excel?
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?

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


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