Workday And Network Day Formula Challenge

Feb 11, 2007

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.

ADVERTISEMENT

Formula Challenge: OFFSET Application

Sep 4, 2008

i have a rather tricky formual challenge.

I present the triangulated data below, years-by-years....

View 9 Replies View Related

Workday Formula On A Conditional Formatting

May 29, 2009

I am having an issue, because aparently workday formula does not work in conditional formattings.
I have the two formulas below to use in conditional formattings, but I can't figure out how to re-write them so that Excel will allow me.

View 5 Replies View Related

Formula That Would Calculate What Workday A Date Would Be

Feb 15, 2007

I am trying to find a formula that would calculate what workday a date would be.

So if the date is 2/19/2007, I want to know that is the 13th workday.

View 9 Replies View Related

WORKDAY Formula Replacement W/o Use Of Analysis Add-In

Jun 17, 2008

My boss has made it clear I can't require our people to use the add-in.

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 add-in... but I am not finding it.. can someone help?

Using the add-in, this is what I have:

myCell.FormulaR1C1 = "=WORKDAY(RC[1],-RC[2])"

View 9 Replies View Related

Workday Function/Formula Ignoring Weekends

Aug 8, 2006

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 7 Replies View Related

Excel 2010 :: Date Formula That Functions Like WORKDAY But Includes Weekends

Jun 24, 2011

Is there a formula similar to WORKDAY that would include weekends and make something due on the next business day? For example, I have a bill due on 6/1/11 and I need to follow up 5 days later - which would be normally 6/6/11 but the WORKDAY formula makes it 6/8/11 as it is adding 2 extra days.

I am using Excel 2010.

View 14 Replies View Related

Math Challenge: A Button To Sort Numbers

Dec 11, 2008

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 9 Replies View Related

Macro Challenge-Copy Range Of Non Empty Cells From 2 Sheets

Mar 20, 2007

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 "non-empty" 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 9 Replies View Related

WorkDay Function VBA

Jul 31, 2008

The function is to show the next workday date in cell a1

View 15 Replies View Related

Workday Function Use In VBA

Aug 15, 2006

I have a spreadsheet setup that uses the workday function. Shown as follows: =Workday(P$2-1,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/en-us/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 7 Replies View Related

Conditional Format If Not Workday

May 26, 2009

Maybe I'm getting a blank, maybe the answer is so easy, but lately I canīt solve this: How do I use conditional format to change cell color if cell value (date) is not workday?

View 4 Replies View Related

Calculating If A Period Is During A Workday

Jul 22, 2009

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. (1400-1100)

other example:

start: 17.07.2009 11:00:00
end: 18.07.2009 11:00:00

result is 1900-1100 + 1100-1000

= 9 hours

because it is during two workdays...

is there a way to calculate this in VBA?

View 14 Replies View Related

Workday Function Or Similar

Jan 3, 2007

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 9 Replies View Related

Convert Date To Workday

Nov 18, 2008

If I have a date in cell G6 of 10/7/08, how could I get the workday of 5 in cell H6? I tried the workday function, but have been unsucessful so far.

View 12 Replies View Related

Macro For Workday Dates

Jun 11, 2008

Im trying to display dates in excel sheet using macros. can anybody help me how to show Workday(x,x) in VBA.

View 7 Replies View Related

Counting Back And Returning Workday

Nov 27, 2012

I'm using the following to return the previous TUESDAY in my Event Date cell F43

=$F$43-WEEKDAY($F$43+4)

It works great but if my event date is a Wednesday I need to return the Tuesday in the week prior not in the Tuesday immediately prior.

That is, If the event falls on Wed 22 May 2013 the cell needs to return Tuesday 14 May 2013 not Tuesday 21 May.

View 3 Replies View Related

Filesearch Object With A Datestamp (workday-1)

Dec 1, 2006

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 9 Replies View Related

Workday Holiday Trigger Date

May 21, 2009

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 9 Replies View Related

Function That Is The Equivalent Of WORKDAY But For Hours Instead

Mar 14, 2007

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 6 Replies View Related

Workday - Adding Partial Days

Mar 31, 2007

When I use the formula below it works great;

= J10+(1/$D$10)

J10 = 3-1-07
D10 = 1.5 (Production Rate example .2,.5, 1.5 rate per day) The result is:

3-1-07
3-1-07
3-2-07
3-3-07
3-3-07
3-4-07
3-5-07 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...

3-1-07
3-2-07
3-3-07
3-4-07

View 9 Replies View Related

WORKDAY Function To Calculate The Start Date

Jun 11, 2009

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 11 Replies View Related

=WORKDAY But Includes Weekends In The Specified Number Of Days

Nov 24, 2008

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 non-weekend, non-holiday 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 9 Replies View Related

Calculate Workday Difference Between Dates/Times

Jun 19, 2008

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

View 4 Replies View Related

Force Formulae Recalculation & Return Day Name From Workday Function

Feb 27, 2008

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 non-working days or weekends. (I haven't even gotten to inputting holidays yet.)

View 6 Replies View Related

Network Print Is Required

Feb 23, 2014

I recorded a print macro which prints to my default printer, others will be using this spreadsheet so I really need it to print to the same network printer. The printer address and name is: ironyan-fileBI44

How do I incorporate this into my code?

[Code] .....

View 7 Replies View Related

Working With PC Network Names?

Jun 3, 2014

Is it possible to retrieve the network name of the local workstation and if so how?

View 2 Replies View Related

Saving To A Network Drive

Jan 6, 2009

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 10 Replies View Related

Changing Network Printers

Apr 15, 2009

I have a command button in a spreadsheet with the following code attached to it

View 4 Replies View Related

VBA - Opening A Workbook On A Network

Sep 29, 2009

As above i have a macro which opens a workbook which is on a network drive. This works fine for my multiple users...however i have discovered that if they do not have the drived mapped with the same drive letter at the start...it will throw up an error advsing that the file does not exist. Is there any way to by pass this.

Ex - "TestFile.xls"

File is stored in "E:NetworkTeam1Testfile.xls"

However, some users have access to this file and it is mapped as E:, but some users have been mapped as F: The advisors who have been mapped as F: cannot access the file. Is the VBA/Macro smart enough to bypass the drive letter and just search the rest of the past...NetworkTeam1Testfile.xls".

View 2 Replies View Related

How To Get To A Computer On Our Network Using Code

Jan 29, 2007

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 9 Replies View Related

How Can I Identify A User Over A Network

Apr 28, 2008

I have written a function (with the help of the good folk on this wonderful message board) that is able to check whether a given spreadsheet is currently being used by someone at my work. There are potentially multiple users for the workbooks that my system needs to access. FYI, I work in a bank so we have a pretty good network.

The code for my function, as well as the TestOpen(-) function that it uses, is below.

Ideally, if a workbook my system needs is currently being used on someone else's computer (Case=1 in Select statement, then Err0) I would like to be able to have a userID returned to me so that I know who I need to call to shut the model.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Function Open_Selected_Drone__EXEC(fs, fileLocation, fileToOpen, testOnly)
Application.ScreenUpdating = False
myName = ActiveWorkbook.Name

Application.StatusBar = "Checking availability of " & fileToOpen
iOpen = TestOpen(fileLocation & "" & fileToOpen)

Select Case iOpen

Case 0: ' the file has been found and is not in use
rv = "not in use"
If Not testOnly Then Workbooks.Open Filename:=fileLocation & fileToOpen, WriteResPassword:="j"

The old version of this message board used to have an "insert code" button. I cant see it anymore. Am I simply meant to paste code into this message box now?

View 9 Replies View Related

Call A Sub From An Xls File On The Network

Oct 1, 2008

I'm trying to call a sub in an exce. file that resides on our network drive so that multiple users can access the programs that I develop and I can get VBA to open the file but I can't get it to open the sub titled "DCU". I keep getting the following error.


Cannot run the macro DCU. The macro may not be available in this workbook or all macros may be disabled.

Here is my code that errors.

Option Explicit
Sub DESCRIPTION_CLEANER_UPPER()
Application.Visible = True
Workbooks.Open Filename:= _
"\Cansvp01grp_01fCommonCommon-PartsPrcngMacrosMacros.xls"
Application.Run "DCU"
End Sub

View 9 Replies View Related

Active Network Printer

Oct 30, 2008

Is there a way to test for an active default network printer?

In my module the code sets the paper size and a few other printer settings, if my default network printer is down or offline the code fails.

If I could test for active default printer I could remove this error.

View 9 Replies View Related

Use Network Id To Unprotect Sheet

Jun 1, 2006

We have created a spreadsheet that needs to be protected as it is viewed by several users. We would like, if possible, to update this daily with as little human intervention as possible.

Can we use VBE and the on worksheet open event to unlock the password protection, update the sheet and then lock it again? I know that you can read who opens the sheet using code similar to the following but don't know whether the password protection can be "unlocked" utilising this method.

View 6 Replies View Related

Template Read Only On Network

Jan 3, 2007

I have 2 workbooks, one containing one worksheet that acts as a Template a second that has some code attached to a button that uses the Template to create new worksheets based on the Template. The problem I have is that when I run the code over a network I always get a read only worksheet created. I need it to be readwrite. I have used sharing on the Folder where the 2 workbooks are located so it accessable over a network. There is no problem when I run the code on a standalone machine.

Private Sub CommandButton1_Click()
Dim tClient
Dim tAddress
Dim tPhone
Dim tItem
Dim cOriginalLoan
Dim cAgreedInst
tClient = Client
tAddress = Address
tPhone = Phone
tItem = Itm
cOriginalLoan = loan
cAgreedInst = Inst
Sheets("swbrd").Select
PathName = Range("A196").Value 'Here is where the full path to the Template is found.................

View 3 Replies View Related

Macro Does Not Run On Different Computer In Same Network

Jan 20, 2007

I wrote a macro that runs perfect when started from my pc, but if started from a different ps which is logged into the same network it doesn't run, also the macro is stored on the server. I have a code in the private section of the workbook:

Private Sub Workbook_Open()
Call FINAL
End Sub

then the macro 'FINAL' is stored in the 'Modules' section and starts with:

Sub FINAL()
'this part opens the Edit/Links dialog from which the user has to choose the relevant model
Dim Which_financial_model_contains_the_data As String
ActiveWorkbook.ChangeLink Name:= _
"T:DEALSSSalsa (Project)Financial modelsProject Salsa Model - BASED ON SPONSOR CASE - b - 20061214.xls" _
, NewName:= _
"Which_financial_model_contains_the_data" _
, Type:=xlExcelLinks

View 6 Replies View Related

Is File Open On Network

Jun 28, 2007

when I open a file, called "A", I want it to automatically open another file, which is shared on the network, called "B". I've tried using the "IsFileOpen" routine and a few other sets of code that I've found on the internet but none of them solve my problem.

If "B" isn't open on any computer and I open "A", there is no problem, and if "B" is already open on my computer when I open "A" again, there is no problem, but if "B" isn't open on my computer and is open on somebody else computer, "B" won't open on mine when I open "A", the code assumes that because "B" is open on someone elses computer, "B" is already open on mine.

View 9 Replies View Related

Install Network Printer Via VBA

Jul 26, 2007

I have a new workbook that needs to be printed to a specific printer regardless of who the user is. I am happy with the method used for doing this using the PrintOut function, but am trying to work out what to do in the event that the user does not have this printer installed. The logic, not in true VBA syntax, is as follows;

' Check Printer
If <This Printer> Is installed Then
Printout using <This Printer>
Else
Install <This Printer>
Printout using <This Printer>

I found a useful post about ascertaining whether or not a printer is installed, but can't find anything about actually installing a printer via VBA.

View 7 Replies View Related

Is File Open Over Network

Sep 6, 2007

I want to be able to check when workbook opens if a particular Excel file is open or not by having a true or false in a cell. This works fine if I have it open on same computer, but if I log on to another computer over a network and run same workbook it won't pick up if file open or not. I have tried various ways from this forum for file paths over network including eg. C:file or '\path on networkfile or N:pathlocfile etc. Nothing apears to work. If I check to see if file open from a different computer than the one the file is open on I receive standard message file already in use etc.

View 2 Replies View Related

Check If Connected To Network

Jan 11, 2008

I have created an application for users that requires the user be connected to the network. I wanted to do a check to see if they are connected, and if the user is not connected they would get a message box telling them that they need to be connected to the network, then the workbook would close. Below is the code I have, but when a user is not connected they do not get my messagebox, instead they get the excel error message "excel run-time error '52': bad file name or number", and the debugger line that gets highlighted is in the function DirExists that checks the length of the directory of the file path: Len(Dir(strpath))

Option Explicit
Function DirExists(strpath As String) As Boolean
If Len(Dir(strpath)) = 0 Then 'this is where the debugger highlights
DirExists = False
Else
DirExists = True
End If
End Function

Private Sub Workbook_Open()
Dim strpath As String
Dim strfile, strfile2 As String
strpath = "\n530fs1PCLFileSharespcl_repositPricing_ToolsPAT"
If Not DirExists(strpath) Then............................

View 2 Replies View Related

ADVERTISEMENT