I need to create a formula that calculates the number of days a person has worked within a quarter, if they have also left the organisation within the same quarter.
For example, I have someone who left on 26/08/08. I need to establish how many working days this person actually worked within the quarter (01/07/08 - 30/09/08). I need to do this for a large number of staff, so would appreciate it if anybody can let me know whether there is a formula that would calculate this.
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
I'm basically looking for a forumla that will count each employees total scheduled work days for the month inserted and then depening upon the day it is will show how many days the employee has left to work for the month.
I am using the following formula in Column E to calculate the difference between an employees start time, and their previous shift end time in order to work out how many hours rest they have had:
=IF(A4=0,"",IF(I4=0,"",MOD(I4-B4,1)))
My Current Formula works fine for same day calculations, but if an employee finishes work at 18:00 on Friday and starts work at 22:00 Saturday night, Excel calculates the Total Rest hours as 04:00, when in fact they have had 28:00 hours rest...
Is there a way of calculating the hours difference between two dates?
I have attached a sample of my spreadsheet to illustrate
I can do the timesheet formula for adding the hours worked as follows:
Start Finish Total 08:45 17:15 8.5
However, it doesn't work when I fill in a whole week work of hours in this format:
Start Finish Total 08:45 17:15 08:30
It works on a daily basis, but when total hours exceeds 24, the formula get's all mixed up - how to I format the total column to account for every 5 minutes worked, which you can't do when converting to decimal??
This spreadsheet calculates hours worked great for first and second shift but when you enter times for third shift it goes all whacky with the outcome.
I need to worked out Hours worked in a timesheet. This was the easy part, the hard part is the clause tha HR threw in, which is:
If you have worked and 8 hour day WITH 1 hr lunch then you qualify for overtime. if you work a 8 hr day and work through your lunch (1 hour) (so equivelant to 9 hrs) you still do NOT qualify for overtime there for Overtime = 0.
This is cause some people work though their lunch to get overtime, but legally they have to have a break so we are not paying overtime for it. I have basically tried in a formula to replicate this but it works with some data and not with all. attached is an example, as you'll see the formula works in some cells, but not others.
I have a minor assignment at work. The manager has given me a sheet that lists each employee's start date, and he wants to know what percentage of them have worked here for more than 30 days, 60 days, 90 days, etc. I know a long way to do it, but there's several hundred names, and there must be a quicker way. Is an array formula the answer? I need a non-VBA answer, but I'm not opposed to learning a VBA method (no pun intended).
I need to be able to calculate the actual number of days worked for an employee over a specific date range. I have a data sheet containing employee, transactions, and date of transaction (date only, does not include time). The actual number of days worked may fluctuate due to time off or holidays, so I need to be able to calculate this individually by employee. For example, with columns Employee, Transaction, & Transaction Date:
I'm trying to create a macro that will take name values from a list (Last name, First name) and then find these particular names in a long list of names in another sheet. Once all the occurrences of the name are found, the macro would need to add up all the hours that the person has worked during a given month. For example, I have Jane Doe and John Smith on my shortlist, the macro would have to take the name of the first person, Jane Doe, find all the occurrences of that name in the second sheet that contains names, dates and hours worked, then add up all the hours worked by that person and return the total hours worked value for that parson. Then take the name of the second person, John Smith, and do the same, and so on. Is it possible to do it with macros?
If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked.
It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work.
A2=04:00 A1=23:00
Using (A2-A1)*24 give me -19.00 hours
My SS macro has a line:
s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked
Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to the next?
A B 1 4/1/09 12:15 2 4/2/09 10:00 3 4/4/09 8:10 4 4/6/09 9:00 5 4/8/09 5:00 6 4/11/09 7:00
I need to add up hours worked of last 3 days including current day. The date column does not contain everyday's date. How do I formulate a cell for date entry and another cell will show the sum of last 3 days including date not shown and date entered? E.g. If I enter date 4/6/09, result should be 17:10 hrs; 4/11/09, result should be 7:00.
I need creating a formula that will tell me the total number of employee hours worked during a certain hour (6:00 AM) for a particular department. Some employees clock in at 6:00 AM, some clock in at 6:30. Here is the current formula I'm using which only tells me the amount of employees in the department not how many hours worked . I have three employees that clock in at 6:00AM and one at 6:30AM, the total I'm looking for is 3.5 but my formula gives me the result of 4.
C5:C1446 is a list of departments T1503 is the 6:00 AM D5:D1446 are the Clock In times K5:K1446 are the Clock Out Times
Is there a way to conver a persons time spent (given in weeks) to adjust/convert to show per month. Attached is the sheet. Do note that week 2/25 - 3/1 is a combination of Jan and Feb so hours should be logically divided into jan and feb...
Name 2/18 - 2/22 2/25 - 3/1 3/4 - 3/8 3/11 - 3/15 Feb mar Tom 40 10 0 20 ?? ?? name 2/18-2/22 2/25 - 3/1 3/4 - 3/8 3/11-3/15 Feb Mar
I know the title is a bit vague, but I cant think how else to word it!
I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target.
I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.
I have been working on a timesheet but the problem I have come across is calculating actual hours worked only in the core hours and any work outside the core hours is calculated in the outside hours column. A standard work day is 7.6 hours working between 8.30am and 5.00pm. However if someone was to commence work either before 6am or after 8pm this is outside of core hours. I have attached an example of my timesheet for you to see what I am talking about.
What I would like to do is insert a formula into column E that would calculate the number of days from when the count is at 50 to when the next count is i.e. for part 6689841 the time between when the count was 50 and when it was at 20, so the difference between 21/01/2008 and 08/08/2008 in the above example. It does not matter what the count number is (it could be any number except 50), I'm only concerned with every time the count is at 50 how long it is until the next count and to show the result in days.
I have a spreadsheet where I record individual staff start time, end times and the length of their break. From this I caculate the hours they worked for the day.
E holds Start, F holds End and G holds Break length. Thus using the following forumula gives me the number of hours and minutes:
I am trying to calculate the number of days that items are were held in inventory as of a given date. See below for some sample data.
'As Of' - the date in question # Holdings - Using sumproduct to find items bought on or after the date AND sold after the date Held Days - ? Total number of days that the six items were sitting in inventory as of the date.
As Of #ItemsHeld Days 5/21/98 6
ItemBoughtSoldTotal Inventory Days 19/3/919/3/961827 21/2/921/2/971827 31/2/921/2/971827 43/2/928/21/951267 55/1/925/1/971826 65/1/925/1/971826
If I had two dates in two separate cells , so E2 is the 01/10/08 and F2 is 06/10/08 and I want to work out that their is a difference of five days what would the sum be? Also is there anyway I could factor into that sum what is pure working days as opposed to weekends?
I am trying to calculate the time elapsed. I have included a caption to show my formula. The problem I have is that some times are showing > 60 minutes instead of increasing the hour. When my day go over the midnight hour I get a negative number.
I recently manage to create a spreadsheet. On the spreadsheet what I am looking to do is once I change the year in cell U1 from 2010 to 2011 to automatically change the days and the date number, and where Sat and Sun preferably to auto-fill in yellow the whole column within the table as you can see in the spreadsheet.if not then just do not display Sat/Sun columns at all..
I need a formula that will allow me to put a date in cell a2, and in cell a3 put the the number of days between 2 dates. Example.. For example (A2) shows 08/06/08, a3 to show the number of days from 08/06/08 to 08/14/2008--(a3 )14 and (a4) to shows the number of days from 08/06/08-10/05/2008 ---(a4) 60 days.
I am trying to calculate dates that will happen 16 days from date specified in column A (A23:A200), based off a certain entry in Column B (B23:B200), which is named in cell P5. I can get the date by doing the specified date + 16, e.g. =IF($B$23:$B$200=$P$5,$A23+16,"")
The trouble I am running into is not having the date populate until it has reached 16 days from the dates in column A, show blank if possible until 16 days from date in column A. The dates in column A will always be previous to todays date.
I have a static date and military time in B6 (5/10/07 18:00) I have to write various formulas to reflect 1 month (whatever calendar month it ends up in, so not necessarily 30 days) minus 7 days and then the same formula plus 7 days. How is this written?
