Workday And Network Day Formula Challenge
I am trying to create a sheet for a project that will identify dates a project will conclude omitting weekends and holidays. I keep getting an error with the formula I have.
In my spreadsheet:
A1 is the start date
B1 is the number of days after the start date.
The formula I am using:
=WORKDAY(A1,NETWORKDAYS(A1,A1+B1),{""4/6/2007","5/28/2007", "7/4/2007","9/3/2007","11/22/2007","12/25/2007","1/1/2008","1/21/2008","3/21/2008","5/26/2008", "7/4/2008","9/1/2008","11/27/2008","12/25/2008"}"}+0)
The error I get is with the parens (A1,A1+B1).  at least the parens are highlighted in purple.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Force Formulae Recalculation & Return Day Name From Workday Function
I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded). I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore nonworking days or weekends. (I haven't even gotten to inputting holidays yet.)
View Replies!
View Related
WORKDAY Formula Replacement W/o Use Of Analysis AddIn
My boss has made it clear I can't require our people to use the addin. I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis addin... but I am not finding it.. can someone help? Using the addin, this is what I have: myCell.FormulaR1C1 = "=WORKDAY(RC[1],RC[2])"
View Replies!
View Related
Workday Function/Formula Ignoring Weekends
I'm at my wits end trying to make a workday formula work, but am having no luck. I've searched the other posts, but did not quite find a match. I'm trying to subtract a number of days from a workday function, but it seems to bypass ignoring the weekends (as is supposed to be done automatically with Workday). Here's the formula I'm trying to use: =WORKDAY(H5,L5,' Reference Info'!$E$110:$E$117)(I5/8) I5/8 is a column with hours  this just calculates a number of days to subtract based on the number of scheduled hours. The formula will calculate, but it still ignores weekends. I've tried burying the I5/8 within the parenthesis, moving it around in the formula,
View Replies!
View Related
Math Challenge: A Button To Sort Numbers
I need code to attach to a button! In cell A1 I enter number of teams. In Cell A2 I enter number of games to play. Is there a way for me to click a button and... In columns A and B it will give me a list of teams to play each other based on the criteria above. In Column A will be home team and B is Visitor. Example I enter 9 teams with 12 games to play, hit the button and it will list the games in columns A and B.
View Replies!
View Related
Macro ChallengeCopy Range Of Non Empty Cells From 2 Sheets
first time posting. Need some help. I have a workbook with two sheets  (say sheet A and sheet B) that I need to copy a range from and paste (concatonated) into a new workbook. The thing is I want to copy the "nonempty" cells in columns d and e of each sheet  another words  I dont want to specify a specific cell range because users will be periodically adding new rows. Then I want to paste the "combined" from both sheets into a new sheet in a new workbook.
View Replies!
View Related
Day Accrual Formula
I need to make a formula that will accrue vacation days automatically. We accrue days on the 15th of the month( If you start on February 15th, you have accrued the days for February). If the person has been here for 3 years they accrue 15 days a year. If they've been here for 7 years, they accrue 23 days. If they've been here for 10 years they accrue 30 days a year. All days are accrued evenly over the 12 months. For example If someone started 3 years ago on March 3rd, starting March of 2007, they will be accruing 23/12 days a month, but for January and February, they will accrue 15/12 days a month. The formula must work off their start date.
View Replies!
View Related
Displaying Day As A Formula
I'm working with a worksheet with one sheet per day of the month (we use a master, copy/paste, change the copy to the current month, and go from there). I've already got it to figure the dates based off (really difficult).
View Replies!
View Related
Formula: Day Into Month+year
i need to put a date into a month +year like this: 1/06/2008jun/08 i did this with a format date. After that i tried to copy/paste special values. So i can make i pivot table on the jun/08. But the problem is he still see it like a date. MaandTotal jun/081jun/0810 he sees that 1/6/2008 is not the same as 2/6/2008 that is why i got 2 lines of jun but i just want one and that he counts all the junes together
View Replies!
View Related
Formula For Total Figure Each Day
I need a single formula that will give me a total figure each day. I am trying to get a total weight for each days sales Sheet 1 holds the unique ID and total items sold for each day Sheet 2 holds the unique ID and the weight of each item. I have got as far as creating helper cells but this is making the whole sheet way too big. I know there is a more efficient way .....
View Replies!
View Related
Last Working Day Of Month Formula
I am trying to create a formula to work out the last working day of each month, but I can't come up with anything that is less than the 1,024 formula limit. I am using a UK calendar so the only two holidays to effect the last working day of the month will be Easter and the last Monday in May. This is the formula I am using which takes account of Easter, the checking for the last Monday in May is simple enough in another column, but I would rather keep it in one column, just wondering whether there is a simpler way of doing this. A1 is a specified date and then I would like to work out the last working day of the month, on a new row, for up to the next 50 years....
View Replies!
View Related
Formula To List The First And The Last Day Of Each Month
is there a formula to list the first and the last day of each month and for every year?. example: if i put in cell A1: 01/09/06 >01/September/2006 the result should be: A2: 30/09/06 > 30/September/2006 A3: 01/10/06 A4: 31/10/06 A5: 01/11/06 A6: 30/11/06 A7: 01/12/06 A8: 31/12/06 A9: 01/01/07 A10: 31/01/07 A11: 01/02/07 A12: 28/02/07 . . . and so on how to do that with a formula?
View Replies!
View Related
CreatE A Formula That Would Total A Rolling 90 Day
I am trying to do is create a total of only the last 90 days of entries from a list of $ amounts. Attached is an example. We are going to start keeping track of our cashiers variances but at the top of the sheet we only want to know what their last 90 days of entries equal. This would be considered a rolling 90 days, so older variances drop off after 90 days, they would still be left on screen but would not effect the main total.
View Replies!
View Related
Day Of Week day Of Month List Auto Setup
What I'm after is a sheet that self generates the day of the week in column A and the day of the month in column B. I have a month long sheet where daily entries get made in the DOW row, the day of the month is a reference. I have a macro to generate a new sheet for the next month and would like to auto populate the DOW and DOM. This typically gets done on the second day of the month (data from the first day is entered on the second)
View Replies!
View Related
Formula Columns Where My Workers Record The Amounts Of Work They Do During The Day
I have three columns where my workers record the amounts of work they do during the day. The columns are labelled as follows: Column A – “Correspondences’’ Column B – “Linking’’ Column C – “Allocating” When a task has been completed an “X’’ is put in the relevant column (which is then summed at the bottom). I use these sums to calculate each workers productivity in the following way – number of “x’’ divided by number of days worked. This is a simple formula, however, each “x” now equates to time – which is causing me problems. An “x’’ is Column A equals 1 hour, Column B 1 hour and Column C 2 hours. I am looking for someone to help me create a formula which will calculate all possibilities in the above situation above. Please bear in mind that a worker can do one of these tasks during the course of a week, two or all three .
View Replies!
View Related
Calendar From Formula :: Nth Day Of Week For A Month And Year
I'm using this formula from [url] Nth Day Of Week For A Month And Year This formula will return will return the date of Nth dayofweek for a given month and year. For example, it will return 26March98 for the 4th Thursday of March, 1998. Daysofweek range from 1 to 7, with Sunday = 1 and Saturday = 7. =DATE(Yr,Mon,1+((Nth(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+ (DoWWEEKDAY(DATE(Yr,Mon,1)))) Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and DayOfWeek. I would like to be able to change the year and month in A2 and B2 and have the calendar change. I will be inserting rows between the weeks to return appts, if I can get this part working. I could make a new tab for each month, but I thought I would give this a try....
View Replies!
View Related
Calculating If A Period Is During A Workday
I have two dates in this format: start: 17.07.2009 11:00:00 end: 17.07.2009 14:00:00 i need to check if the period in between those is during a workday. our workday starts at 10:00:00 and finishes at 19:00:00 so my expected result is 3 hours. (14001100) other example: start: 17.07.2009 11:00:00 end: 18.07.2009 11:00:00 result is 19001100 + 11001000 = 9 hours because it is during two workdays... is there a way to calculate this in VBA?
View Replies!
View Related
Workday Function Or Similar
This is probably a simple question, but one I can't figure out after several hours. I am creating a spreadsheet focused on certifications. I need to take the date each initial certification was achieved, and add x years to each cert, which is simple enough. The sticky part is that the (date + x years) can't logically be a weekend; if it is, I need to subtract one or two days from the result, so that it's the *weekday* immediately prior. e.g. if my initial certification date is: 1/23/2006 and my certifcation expires in four 4 years: A47= date(2006,1,23) h47= 10 =DATE(YEAR(A47)+H47,MONTH(A47),DAY(A47)) Result = Saturday, January 23, 2010 If the result is a Saturday or Sunday, I need to subtract one or two days from the date. In this example, I'd like: Friday, January 22, 2010 as the result. I've been playing with 'if', et. al., to no avail.
View Replies!
View Related
Workday Function Use In VBA
I have a spreadsheet setup that uses the workday function. Shown as follows: =Workday(P$21,V2,Z2:Z$11) P2 is a set date in which the location will not change. Z:Z is a list of Holidays. V2 should offset by 1 row. I want it when a command button is hit it runs that formula untill the rows value = "End". I have found "Working with Workdays" information at MSDN's website http://msdn.microsoft.com/library/default.asp?url=/library/enus/dnvbadev/html/workingwithworkdays.asp It seems to be for access but I'm trying to use in Excel and I am getting errors/confused. The main error I'm getting is Compile Error: Method or data member not found because of rst.FindFirst.
View Replies!
View Related
Array Formula: List The Number Of A Specific Class For A Particular Month (any Day)
I'm having difficulty creating an array formula. In a multicolumn sheet, I am looking at a column with classes and a column with a date (in the format 7Oct09). I need to list the number of a specific class for a particular month (any day). I have tried the following which gives only the number of classes: =COUNTIF(A4:A2500,"AA")+COUNTIF(H4:H2500,"10/??/09") and =SUM((A4:A2500="AA")*(H4:H2500="??Oct??")) which gives me 0. Maybe an array formula is not the way to do this.
View Replies!
View Related
Count Formula Spreadsheet That Is Being Used For Tracking Work Completed Each Day In A Week
I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16. As a side question, will count work for a range of data or only one column or row at a time?
View Replies!
View Related
Formula Fix (today)(month/day Of Hire) For Vacation Accrual
I'm trying to create a formula in Col D that will give me the difference of (Todays) Month/Date and (Date of Hire). The shaded rows at the end do not provide a correct answer. How do I make the Column formula eliminate the 1 (due to hire mnth/day falling between 1/1 and current mnth/day). I've put about 30 hours in this formula, tried it many different ways. . . and realize I just can't get it. I'm all for redoing the whole spreadsheet if you have a better way of doing vacation accruals
View Replies!
View Related
Workday Holiday Trigger Date
I am trying to work out a formula calculating dates. basically a piece of work / report is due for completion twenty working days from a trigger date. And I need the formula to calculate this due date. Im using the workday function, which calculates a date using the number of working days given after the trigger date. this would work fine, except that the trigger date needs to be included in the calculation (inclusive). I have tried changing the value for the number of working days to nineteen, which would logically give the right answer (20 days including the trigger day). BUT the bloody thing doesnt work, because the trigger date can be on a weekend or holiday!! so then it calculates 19 days (as it should) but the result is wrong because the trigger day isnt included (as its a holiday/weekend).
View Replies!
View Related
Filesearch Object With A Datestamp (workday1)
I need to be able to open a filenamed in the following format. c:/workbook20060112.txt. However the lsat part of the string is dyanmic and works with one business days lag (hence format sheet (date, "yyyymmdd") ) is not approriate. i also have the problem that that MS Excel does not have a holiday calendar (so this would not work over bank holidays/easte/exmas ect). The folder also contains archived files as below. c:/workbook20060112.txt c:/workbook20061130.txt c:/workbook20061129.txt I have been told about a filesearch object as well as a filesystem object. What situations would you use one over the other? What I want it the most upto data file to pick up, but I dont want to do a loop where it look through all the files or something that counts down from today until it 'hits' the correct file.
View Replies!
View Related
Workday  Adding Partial Days
When I use the formula below it works great; = J10+(1/$D$10) J10 = 3107 D10 = 1.5 (Production Rate example .2,.5, 1.5 rate per day) The result is: 3107 3107 3207 3307 3307 3407 3507 and so on... however, when I try to include only workdays, it does not calculate correctly. = WORKDAY(J10+(1/$D$10),1,Holidays!B75:B88) with the same definitions as above the result is... 3107 3207 3307 3407
View Replies!
View Related
Function That Is The Equivalent Of WORKDAY But For Hours Instead
I am in need of some excel advice relating to date calculations. Basically I need a function that is the equivalent of WORKDAY but for hours instead. I have a series of events that take a certain length of time to complete, most of them less than a day but some more than. By way of example see the screenshot below: In reality the last three operations would have to take place on the 27th of April, with the Welding operation starting on the end of the 25th around 7pm. The plant is running a 24 hour day, and works 5 days a week. How can I calculate the times in hours offset rather than going day by day? I need to account for * Weekends * Fixed Holidays * Operations running as seamlessly as possible Any advice welcome. I have attempted to use WORKDAY with the number of days to deduct rounded to the nearest day and then subtracting the operation time but this results in errors where operations would cumulatively go over a working day. The objective is by knowing when the end product is needed and knowing how long each operation takes it is possible to discover when to start manufacture. VBA or Formula code is fine as this will be integrated into a VBA project.
View Replies!
View Related
WORKDAY Function To Calculate The Start Date
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends? For example, Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009 Start Date: ??? Finish Date: 8 Jun 2009 Duration: 5 days The Start Date should be 30 May 2009. Seems like I am not able to use WORKDAY function to calculate the start date.
View Replies!
View Related
=WORKDAY But Includes Weekends In The Specified Number Of Days
I'm looking for a function similar to =WORKDAY but with a slight variation. I want the formula to return the date that is the closest nonweekend, nonholiday day that is a specific number of CALENDAR days from the start date. So the formula does not return weekend or holiday days, but does include them when counting the days away from the start date. For example if the start date was Dec 1st, and I specified 14 days after this date, I would want Dec 15th returned (as the 14th is a weekend).
View Replies!
View Related
Calculate Workday Difference Between Dates/Times
I have a spreadsheet with Received Date/Time in C2 and Resolved Date/Tim in D2, Work Start Time in E2, Work End Time in F2. C2 and D2 are in dd/mm/yyyy hh:mm AM/PM format:04/03/2008 10:27 AM06/03/2008 1:06 PM9:00:00 AM5:00:00 PM I need to be able to calculate the resolution time of each request in hours/mins excluding weekends and public holidays. I've tried using the NETWORKDAYS function but it doesn't seem to pick up multiple days between the Received and Resolved dates/times. I have put the Public Holidays in my spreadsheet ( attached) in AA2AG2.
View Replies!
View Related
Month Day Day Of Week
1 2 3 4 5 6 11/1 11/2 11/3 11/4 11/5 11/6 sat sun mon tues wed thur what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week =$D$1 =$D$1+E51 =TEXT(WEEKDAY(D6),"ddd") cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###.
View Replies!
View Related
Find The High Price Day Of Each Week. Then Report The Median High Day
I have an interesting problem (I hope you find it interesting too). I'm trying to find the historical "high" day of the week for a particular stock I follow. I searched for several hours but was honestly unable to make any progress. Normally I do my own formulas, so other than pasting a rare macro, that's pretty much the extent of my (lack of) experience, ie please assume I am a novice....
View Replies!
View Related
Saving To A Network Drive
We have a form that is completed by our employees and emailed to a central group email address that up to 8 employees within our team can access. When one of our 8 team members open the file sent they need to add comments to it and then save it in a central directory on one of our network drives. Now the problem I have is that we all have the network drive mapped to a different letter. Is there a way that I can have a macro (see below for current macro that does not work) save to a network path and not a drive letter.
View Replies!
View Related
How To Get To A Computer On Our Network Using Code
I have code below that works well most of the time. One of the cases below will not work. It insists on requiring a name and password. (note: it is the only Windows 2000 operating systems) I want to enter this in my case line to automatically add the username and password. Let's say the username is: "dave" and the password is: "happy" How can I add this to this part of the code? Case "DJ" strfilename = "\DAVEJONESDavesProposals" & strfilename Sub Save_and_SaveSalesman() Dim strPath As String, strPath2 As String, CurrPath As String Dim WB1 As Workbook Dim WB2 As Workbook
View Replies!
View Related
