# Calculating Working Days For Multiple People

Jan 1, 2009
I'm using Excel 2003, and successfully working out start date (T2 in the formula below) plus duration in days (U2) minus any non-working days listed in a seperate worksheet. =WORKDAY(T2,U2,Holidays!C6:C17)

Each 'task' is on a seperate row, and the lead officer is named in Column E. How can I get the work day function to include the non-working days for each officer as well as the global non-working days in the seperate worksheet? I thought about using a vlookup, but that only matches the first non-working day for each officer rather than all of the non-working days.

View 2 Replies
ADVERTISEMENT
Aug 15, 2010

how can i calcaulate working hours between two days.

Working Hours 07:30 - 14:30

e.g. mm/dd/yyyy

start time 01/04/2010 17:34:58

stop time 01/05/2010 08:23:35

View 9 Replies
View Related
Jan 6, 2014

I am working on a commission report for our veterinarians. I have a tab/sheet for each year, with a column for each month, a row for each of the veterinarian. Each vet is on the same row, year to year. I need to be able to calculate the %, year to date, for each vet, across the past 2 years. I can do the auto sum, but do not know how to do percentage. I have row 3, column b3 thru m3. I want to do 2012 and 2013. Each sheet is name the year.

View 1 Replies
View Related
Jan 29, 2014

I have a downtime tracking spreadsheet that we want to be able to track the downtime on the line. However, we only want to count the working hours, not overnight, etc. I have added the weekend day work hours and Saturday work hours but haven't been able to figure out how to connect them into a formula with the down time.

View 1 Replies
View Related
May 5, 2014

Phasing Design.xlsx

We have a requirement to spread/phase amounts over multiple columns (representing months) using a weighting (represents working days per month).

We will calculate the weighting result in excel first (represented by a % per month), so the macro will be more of an allocation of row amount against the equivalent column %.

So far i have the following code:

VB:

Sub phasing()

Dim SourceA As Range

Dim Weight_ResA As Range

Dim TargA As Range

Dim i As Long

[Code] .....

View 2 Replies
View Related
Dec 11, 2013

I've put together a spreadsheet look at the time taken for a vehicle to get from one building to another when called. I've set up conditional formats where the timings turn orange after 5 minutes and red after 10 minutes taken. What I also have to show though is who is taking the vehicle and break it down to show any delays to patients. Is there a formula that will automatically tell me when there has been a delay to patients?? Be amazing if there is as it would save me uber amounts of time as i have to collate a whole years worth of data!

I've attached a small sample of what I've done to show you how the spreadsheet looks.

sample.xlsx

View 5 Replies
View Related
Aug 19, 2014

I am trying to count the number of extra people scheduled for each hour of the day, from 2 dropdowns in A3:B7

The formula in (1) D11:D26 works, but when the shift passes over the midnight it will not count accurately.

The formula in (2) E11:E26 also works well, but only counts the number of times a particular hour is mentioned, but not the number of extras in C3:C7.

The results in (3) F11: F26 are what I need, which is based on looking at the times from the dropdowns and using the number of extras in C3:C7.

View 2 Replies
View Related
Jul 31, 2014

I've got the below so far, but where it says V2>=(Q2+5) , I would like it to add 5 working days instead, is this possible?

Code:

=IF(AND(T2="Awaiting",V2>=(Q2+5)),"Overdue","Raised within 5 Days")

It would also be great if there is a way to say that if T2="Not Required, then put "Not Required" in W2 (where the IF statement is)

View 4 Replies
View Related
Sep 15, 2008

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.

View 9 Replies
View Related
Dec 25, 2012

I have a sheet in which it is separated by zone area, name (about 200 names), and email. I am trying to add people to an email based on zone. For instance:

Nevada Billy Billy@gmail.com

California Sal Theemail@gmail.com

New York Jim Jim@gmail.com

Nevada JOe Joe@gmail.com

Arizona Alex Alex@gmail.com

So how can add all the people in X zone to a single outlook email (in this case Billy and Joe)?

View 3 Replies
View Related
Apr 2, 2014

I know how to calculate the duration elapsed between start and finish time. But how can I display the total time if for eg it's ends up being 300hrs?

I need the hours spent in the building per day and then the grand total per month.

View 1 Replies
View Related
Dec 12, 2008

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:

View 4 Replies
View Related
Mar 9, 2007

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

View 9 Replies
View Related
Nov 3, 2008

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?

View 9 Replies
View Related
Apr 11, 2014

I am trying to determine a formula that will tell me what the date will be 61 days and 90 days from a selected date. For example:

A1: 04/11/2014

A2: (Date 61 days from date in A1)

A3: (Date 90 days from date in A1)

Is this possible??

View 6 Replies
View Related
Dec 3, 2013

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.

View 11 Replies
View Related
Feb 4, 2010

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

View 5 Replies
View Related
Aug 5, 2008

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.

View 5 Replies
View Related
Mar 9, 2009

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.

View 2 Replies
View Related
Oct 23, 2007

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?

View 9 Replies
View Related
Oct 8, 2008

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.

View 9 Replies
View Related
Feb 7, 2007

is there a way to calculate the number of days between two dates using the excel cells

example.....how do i put in formulas so excel will calulate the number of days between May 3rd and september 19?

i want to enter may 3 in say a1 then sept 19 in a2 a3 should says days between em

View 3 Replies
View Related
Jun 7, 2009

I am trying to send to multiple people so everyone sees each other received the email. How would I add other addresses?

View 2 Replies
View Related
Oct 17, 2009

my spreadsheet is setup with two dates: A date for when someone joins our group A date for when they are released/leave

I want to be able to see exactly how many years/months/days transpired between these two dates. I have tried lots of different formulas with no success.

View 12 Replies
View Related
Jan 20, 2014

I have a file that's almost 3000 rows of data I have attached a test file for the purpose of this request. I am trying to figure out the average days overdue per department whose due dates have not already passed.test (1).xlsx

View 2 Replies
View Related
Mar 16, 2009

I'm trying to calculate the length of a work order to develop an average and future proposal estimates.

For example:

Job received = A1

In work = B1

Job completed = C1

Total days to complete = D1

I know I can enter in D1 C1-A1 and get the correct result but I want the field to calculate A1-TODAY() until a completion date is entered. This would display the number of days the job has been in work until completed. Once completed use the C1 for calculation.

Sorta like

If(C1=">=0" then (A1-Today()) else C1-A1)

I will then use conditional formatting with block colors to indicate whether it is a completed job or in work job.

View 10 Replies
View Related
Jan 1, 1970

It should be deplaying dates of weekly days in Monday, Wednesday and Friday excluding sundays. Or Tuesday, Thursday Saturday excluding Sunday. e.g

Mondays, Wednesdays and Fridays of June

June 1, 2006

June 3, 2006

June 6, 2006

Tuesdays, Thursdays and Saturdays of June

June 2, 2006

June 5, 2006

June 7, 2006

This should be happening after entering any date on the first cell of the List and should accommodate up to 3 months

View 14 Replies
View Related
Aug 24, 2012

I'm building a schedule in excel and am having trouble with the calculation of total hours when lapsing a day, or more specific, adding it into my current equation. For example; Monday they clock in at 5pm and work till 1am. I have found a couple of ways to get the desired out come from this on it's own. I just add the value of a day if the out time is less than the in time, or if the out time minus the in time is less than 0.

However, I need to add this calculation into an equation that is already calculating time elapsed within a single day, turning that into a decimal number and then checking if the total time elapsed is less than 6 hours, if false it subtracts a thirty minute break. Here is the current equation I'm using, I'm sure that there is an if(or, or if(and I could use to make it work when the time lapses 12am while also performing the 6 hour check to subtract the break.

=IF(((F7-INT(D7))*24)-(D7-INT(F7))*24

View 2 Replies
View Related
Mar 14, 2013

I've managed to sort the first half of this issue, in that I can calculate the number of days within a given month which fall inside specified to and from date parameters..

The formula I've used is thus :

Code:

=IF(EOMONTH(A13,0)EOMONTH($I$9,0),0,IF(EOMONTH(A13,0)>$I$9,$I$9-(EOMONTH($I$9,-1)),EOMONTH(A13,0)-(EOMONTH(A13,-1))))))

This examines a date value (A13 in this case) and assess whether any days within that month are between the two date parameters specified in H9 and I9.

H9 = 01/01/2011

I9 = 15/05/2011

Jan-11

31

[Code]....

What I need to do now is specify a second set of date parameters in H10 and I10 and add those into the assessment. The dates will NOT overlap.

So, for example, if I were to set H10 as 01/07/2011 and I10 as 15/07/2011, the results would appear as below

Jan-11

31

Feb-11

28

[Code].....

View 3 Replies
View Related
Dec 31, 2013

Calculating Number of Business Days Between Two Dates?

View 9 Replies
View Related