How To Calculate Leadtime Excluding Sunday
I have a start date in cell A1(5/15/2009) and a end date in cell B1(5/25/2009).
I would like to calculate the leadtime between the 2 dates in cell C1 excluding the Sundays in between.
I have out a formula(in cell c1) as B1A1 but it is yielding 14 days whereas it should be12 days excluding Sundays.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Future Date Excluding Weekends
I am using the same structure to update another column, under the same sub: If Target.Column = 5 Then ' Select Case Range("d69").Value Case "CB1W" ActiveCell.Value = DateAdd("ww", 1, Now()) Case "CB2W" ActiveCell.Value = DateAdd("ww", 2, Now()) Case "CB3W" ActiveCell.Value = DateAdd("d", 21, Now()) Case "CB4W" ActiveCell.Value = DateAdd("d", 28, Now()) Case "CB5W" ActiveCell.Value = DateAdd("d", 35, Now()) Case "NI" ActiveCell.Value = "" Case Else ActiveCell.Value = "" End Select I would like to apply the code to the whole column but if I change Select Case Range("d69").Value to Select Case Range("d2:1000").Value I get a Type Mismatch Error (13)  (on Case "CB1W" )  column d cells are data validation drop down boxes that get their value form a named ranged :Lead_Status. The column is also formatted as Text. (I applied the code to d69 because I have data already in the other cells which I don't want altered till I have the code working) I would also greatly appreciate your advice on how to incorporate the WEEKDAY function to exclude weekends for the DateAdd function.
View Replies!
View Related
Calculate Difference Between Dates Excluding Weekends
I wanted to calculate the difference between dates, excluding the weekends. After much thought i used =NETWORKDAYS(A1,B1)+IF(NETWORKDAYS(A1,A1)+ NETWORKDAYS(B1,B1)=2,SIGN(NETWORKDAYS(A1,B1)),0) that seemed to be working fine. However, I had a problem today with this formula when my start day was saturday (24/05/08) and the last day was monday (26/05/08). What i want is that when i take the difference between these two days, i should get zero as answer as we are not including the weekends in calculation so the start date should ideally be the first weekday which is Monday. However, the formula is giving me 1 as an answer. This formula works fine if i select 27/05/08 as last date in which case i get 2 as an answer which is right. In gist, what i am looking for is a formula which will give me the difference between two dates and exclude the weekends from the calculation. The last date can't be a weekend, but the start date can. Hence, whenever the start date is a weekend, the formula should take monday as a start day.
View Replies!
View Related
Calculate Only Working Hours Between Two Dates Excluding Weekends
How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8  23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates.
View Replies!
View Related
Calculate Business Hour Downtime Excluding Weekends
I am trying to calculate downtime for a Service Level Agreement. The data that I have is the start date/time and the resolved date/time for an incident. The data are in the format  1/1/2008 03:32 AM. The incidents may occur at any time but downtime is calculated only business hours and excludes weekends. I may be required to exclude holidays but that is not a hard requirement right now. What I must be able to specify are the working hours. As example(s)  if an incident is generated on 1/2/2008 4:00 PM and resolved at 1/3/2008 11:00 AM then the downtime is 4 hours.  if an incident is generated on 1/12/2008 4:00 PM (which is a Sat) and resolved at 1/14/2008 11:00 AM (which is Monday) then the downtime is 3 hours. The above assumes working hours are 8:00 AM through 5:00 PM. I have tried using the NETWORKDAYS and WORKDAY functions with little success.
View Replies!
View Related
Calculate Day Difference Between Dates Excluding Certain Days
I am attempting to get the formula in Column "E" (see attached excel file) to work based off the individual reps 'days off / holidays' (Columns K:P (or more as needed) that they might have. the formula I am using (which works fine) is: =IF(C2=D2,0, SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C2&":"&D2)),2)={2,3,4,5,6,7})*(COUNTIF($K$2:$S$2,ROW(INDIRECT(C2&":"&D2)))=0))) My issue comes up when I am coping this formula for say 100+ rows. When I copy the formula to all of the rows, the (COUNTIF($K$2:$S$2) becomes static and does not realize that the row (ie. Row 26 Rep C) has different days off than Rep A. I can manually change the reference for the countif to specify the correct row to their own days but that becomes tedious very quickly. I was wondering if anyone has come across a way to make the countif work based off of that the name in Column B matches the name in Column G then looks 'to the right' and uses the 'days off' that are listed for the corresponding Rep. I have tried a few different ideas but nothing has worked so far.
View Replies!
View Related
Conditional Format If Saturday Or Sunday
I am trying to format a row (B) if B8 = Saturday or Sunday. The value in B8 is derived from a formula. I have tried the following but it only works if cell is typed manually. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo stoppit Application.EnableEvents = False With Me.Range("B8") If .Value = "Saturday" Or .Value = "Sunday" Then Me.Range("B8:J8").Font.ColorIndex = 2 End If End With stoppit: Application.EnableEvents = True End End Sub I also want to change format back if cell changes to MF. Excel 97
View Replies!
View Related
Count Number Of Days Except Sunday
In B13 I have the start date: 12/1/2008 In C13 I have the end date: 1/5/2009 In cell D13 I want a formula that counts the number of days between the two dates that are not Sunday. The start and end dates are included in the count. I have created this formula using Ctrl + Shift + Enter: {=SUM(IF(WEEKDAY(ROW(INDIRECT(B13&":"&C13)),2)7,1,0))} I have also created this formula using Ctrl + Shift + Enter: {=SUM(IF(TEXT(ROW(INDIRECT(B13&":"&C13)),"ddd")"Sun",1,0))} They both seem to work. I get a result of 31. Is there a formula that is better than this, more efficient than this, or "less expensive"?
View Replies!
View Related
Auto Color Whole Column When Found Text "Sunday"
The code below works well also. However, I got 2 problems here. First, I manage to find the word " Sunday", then color the cell below that Sunday. However, I need Excel auto find out Sunday and color that particular columns for me, for example, Sunday found and color start from cell(L55) below that Sunday up to more cells(L59), Range("L55:L59"). Second, I only manage to find first Sunday, I wish the system keep find and keep color untill it found that rows blank, so I using this code. 'Encountered blank cell in row 2, terminate search If Len(Cells(2, LColumn)) = 0 Then MsgBox "No matching date was found." Exit Sub I attached the excel files with sample
View Replies!
View Related
Count Excluding N/A
I have a COUNTA function that I'm using. However, I want to exclude "N/A" from my count, what is the function that I need to use to do that. Currently I have =COUNTA($C$10:$C$122). How do I modify this formula so it doesn't include "N/A" in my count.
View Replies!
View Related
Excluding Cells
I have three columns that i've called 'active period' and 'Date/time' and 'value' The the value of the cells in the 'active period' column depend on the value and time. I want to create a cell that; when i can enter a date, this date will be excluded from the 'active period' column
View Replies!
View Related
Excluding Non Mature Data
I am trying to automatically add up data, excluding that last two data points in a row. And every month add the next data point based on the date of the spreadsheet (top left). see trail below... This sting is going in circles.
View Replies!
View Related
SUMIF Excluding Text
I am trying to sum data in a column based on the absence of a character in a related column. That is to say I need to be able to exclude rows that contain an "m" (there may be multiple characters in the criteria cell)
View Replies!
View Related
Dates Excluding Sundays
I have a worksheet which calculates dates a product will be manufactured based on how many hours the job will run. The first column has the starting date of the job which I input. The second column contains the date the job will finish based on a third column which contains the hours the job will run. The calculated ending date is then put into the starting date of the 2nd job. It continues to calculate down to the last job. This calculation is based on a 24 hour day, 7 days a week. I would like to take out Sunday's in the calculation.
View Replies!
View Related
Averaging But Excluding Zeros
I have a list of numbers that I want to average. Problem is, some of the numbers contain zeros and i want to exclude these entries. So, if I had a list of 6 values and 3 were zero, I'd want to sum the list and divide it by 3 (not 6).
View Replies!
View Related
Sumproduct Excluding An Array
I have the the following formula =SUMPRODUCT((dealer1=$B36),(make=$A36),(company<>nominated)) The first two statements work alone, however I have an array which contains a list of customers that I wish to exclude from the total. I've put those in an array and called it nominated, However I just get #N/A.
View Replies!
View Related
Create List Excluding Zeros
I have a column with values, say column a. In column a are several rows with values equal to zero. I would now like to create a new list that omits the values that are zero, e.g.: original list: 23 0 54 76 0 0 new list: 23 54 76
View Replies!
View Related
Excluding Weekends Without Analysis Toolpak
I currently have a formula like this: e877+choose(weekday(e877),1,0,0,0,0,0,2 this formula is used because I was adding or subtracting a certain number of days from one date to figure out when a certain event would occur next. When the date fell on a weekend, I wanted to make sure that it rolled over to the next closest weekday. HOWEVER, what I also want this formula to do is exclude weekends in the counting of the days. So basically, I want to exclude weekends completely from my formula. example, Printing always occurs 4 days after the p.r. meeting. the p.r. meeting is in e87. so e87 + 4 = the printing date. but I dont want those 4 days to include weekends in the count AND I dont want the final date to fall on a weekend either.
View Replies!
View Related
Finding Maximum Value While Excluding Some Values
I have a row of data with the cells I want to find a MAX for are separated from each other: a1=300 d1=500 g1=800 j1=10,000 I want to find the MAX value for a1,d1,g1,j1, but exclude any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.
View Replies!
View Related
Excluding Specific Items When Dragging Down
Im using the following formula to update our prices in our stock list. =VLOOKUP(C1,'Most Recent Prices 2009'!A:G,6,0) Column C = Unique part number. Problem: I have a tab of 100 or so prices that I don't want to be updated. How can I exclude these pieces of equipment when I drag down the above formula. Assume that these pieces of equipment are in a tab called 'keep' and their unique part numbers are in Column A.
View Replies!
View Related
Excluding Zeros When Calculating An Average
I have a one column of numbers...let's say consisting of six rows the following numbers 12,0,14,25,0,9 if I average this it comes out to 10 how can I average the same rows but exclude the zeros in the outcome(is there a formula to do that?) I would like the answer to be 15.....I still need the zero's in the rows however
View Replies!
View Related
Days Between 2 Dates Excluding Weekends
how to find the number of days between 2 Dates excluding weekends and holidays using vba. I m using excel 2003. All I know is to figure out todays date using the keyword 'Date'. Also, this is my second post and I have tried to comply with the rules regarding the title, but if it is still inappropriate and does not meet 'stating the problem' criteria,
View Replies!
View Related
Calculating An Average But Excluding Boxes With Text
I'm trying to get excel to calculate the average time spent on appointments by officers in a small team. Each of them sees people each day, and as we're very busy I'm eager to get the time spent on each appointment down, by giving them a target of the number of people to see each day. At the moment we gather some stats on this, which show how many people are being seen by each officer. The attached example sheet shows for one week a list of columns with officer's initials at the top, and says how many people were seen by each person each day during that week and then a how many people overall for the week. I'd like to be able to generate statistics on the amount of time each officer has spent on average with customers, and base this on the fact that people have 360 minutes per day (they do 6 hours of appointments and another hour for admin) to see people. I think the spreadsheet attached gives a good idea of the system and what I want to do.
View Replies!
View Related
Weighted Average Excluding High/Low
I'm trying to calculate a weighted average excluding the top and bottom value, and am having trouble creating a formula that can be dragged across. my values are in E2:L17, and the weights are in M2:M17. Basically I want a weighted average of each column, using M2:M17 as the weights. I'm sure someone's done something like this before, care to assist? Example: Data: 1, 2, 3, 4, 5 Weights: 3, 4, 5, 6, 7 Weighted avg ex hi/lo: (2x4+3x4+4x6)/3 = 44/3
View Replies!
View Related
Copy Filtered Range Excluding Headings
I need code to copy rows from a filtered list on one worksheet and insert at a specific point in another worksheet. What I would do if doing it manually is to "select the visible cells, copy, go to the other worksheet, select the point I wanted to insert the data and select Insert Copied Cells". When I try to record this with the macro recorder I don't see the option Insert Copied Cells.
View Replies!
View Related
Rank Formula Excluding Blank Cells
I am currently working on a football spreadsheet ranking players based on their fitness performance scores. For example, there are 14 persons on Offensive Line. I need all their test scores ranked out of 14. However, if there is a blank, I still need the test scores to be ranked out of 14. As of right now, I have cheated, and inputted a random number to bypass the blank: =IF($G12>10, 0,RANK($G12, $G$12:$G$25,0)) But, this now affects my average of the fitness testing scores for that position. This could be avoided if I could find a way around my first problem.
View Replies!
View Related
Conditional Count Excluding Hidden Columns
I was wondering if was possible to create a conditional calculation excluding hidden columns. For example in C4:HA4 the cells may have a number of values L, S, U etc. When a user access the spreadsheet certain columns a hidden based on comparing their PC user ID to an access list in the workbook. What I would like to be able to do would be to count the instances of L in C4:HA4 but exluding the hidden columns. I'd hoped I might be able to use SUBTOTAL in a similar way to Domenic came up with here: =SUMPRODUCT(SUBTOTAL(9,OFFSET(C2:C100,ROW(C2:C100)ROW(C2),0,1)),(C2:C100
View Replies!
View Related
Find Minimum/Maximum Excluding Zeros
I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.
View Replies!
View Related
Locate Last Day Of Month Excluding Weekends
I have a spreadsheet with bloomberg's auto feed, so in col A&B, the price will be updated everyday. I would like to calculate the Monthto Date in Col C e.g. MTD for today's closing = (today's closing price  last month end price)/last month end price how can I constructure a formular in col C which will calculate the MTD automatically when a new price is added to the top everyday.
View Replies!
View Related
Excluding Hidden Cells In Your Data Range
I am applying the sum function to a range of cells in a column. I've Auto Filtered my sheet to only include the data I need. Unfortunately, the sum function is adding everything in that column, even data from the 'hidden' cells that I've filtered out. I need to know what condition or parameter to apply to the sum function (or any other function for that matter), that will only add the data shown on my screen. Not the data that is hidden.
View Replies!
View Related
Count Number Days Excluding Weekends
I am trying to set up a sick leave list which will work out the cumulative total for the last 12 months (from last day of last sick entry). The problem I have, as the is is added to, I need a function/formula to work out 12 months from last date and then add up the no. of sick days from then to the last date. Hope this is clear. The next problem is I need it to work out total days and then without weekends. Below is the chart I have to work out. Column 1 Start date of sickness column 2 End date Column 3 the number of days sick in period (column 2 less column 1 in days) column 4 the number of days sick in period above excluding week ends (column 2 less column 1, less weekends in days) column 5 total sick days in last 12 months from date in column 2 going back a year including weekends column 6 total sick days in last 12 months from date in column 2 going back a year excluding weekends What function or formulas can I use to calculate column 3,4,5 & 6. I am a not very experienecd in Excel
View Replies!
View Related
