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.
I'm finalizing an Excel app - which uses Userforms only. On opening the app I use the code line Application.Visible = False
After clicking on my Label2: (I'm taken to the Web site, but when I click the
< (Back button - Return) I am returned to my Userform BUT my Excel App (Menus, etc) is now showing up. How can I keep it "turned-off"?
Private Sub Label2_Click()
Link = "http://www.jmmay.com/"
On Error GoTo TheEnd
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
'Unload Me
Exit Sub
TheEnd:
MsgBox "Cannot open Hyperlink"
End Sub
The code below (ran from Excel vba) deletes all data from an Access table, paste data from Excel into another table, and then run two Access Macro's and paste data from yet another table back into Excel.
My problem is that when I run the query from Excel, the data returned to Excel is always zero. When I step through the code, and check the Access database, all the steps are carried out properly. E.g, the delete query is deleting all the records in the correct table, the append query is copying the correct data into the correct table in Access. The two macros do run, but then when the resulting table is returned to Excel, it brings back zero's.
Doing all this manually in Access is returning the data correctly.
The two Access macros are running some queries, E.g. the "001 UpdateLatestData" is running two queries and the "002 CreateMonthlyResults" is running about 15 separte queries.
Sub OPENACCESSTABLE_DELETE_ROWS1()
Dim db As Object
Dim accApp As Object
Access_DB = ActiveWorkbook.Worksheets("MACRO").Range("C1").Value
DBPath = "C:MinetteProperty.mdb"
Set oApp = CreateObject("Access.Application")
oApp.Visible = True
oApp.OpenCurrentDatabase DBPath
'Delete Query
DoCmd.RunSQL "DELETE [001 non motor data].* FROM [001 non motor data]"
Set accApp = GetObject(Access_DB) .........................
know if there is a formula for counting back a year from a date?
Basically what I have is we operate a rolling absence/ sick year and so what happens is on a staff members return from absence/ sick we look back exactly one calendar year from the last day of absence and count the number of days off absent in that period for comparison against their paid allowance.
I have posted a mock up sheet and what I am looking for is a formula for column C. The highlighted cells are the period it would be if today was the last day of absence.
Sheet1 Â ABC1Days AbsentDateRolling2201/01/2010Â 3302/02/2010Â 4403/03/2010Â 5207/06/2010Â
6310/11/2010Â 7431/12/2010Â 8601/01/2011249202/02/2011
2410103/03/20112211307/06/20112112510/11/20112413201/01/20121914317/01/201216
I want my worksheet to search a column for a specific date (3-Dec-2008, for example) and count cells containing only that specific date. If it finds the date, I want it to count them.
However, if that particular date doesn't appear in the column, it returns 0. I want the cell to return blank. Basically, instead of giving me 0, I want it to return empty. I've tried IF statements, COUNTIF statements, combinations of IF/COUNTIF statements.
I have managed to get a nice array formula to look up count all the Rs As & Gs in some cells and return whichever there are most of (the cells are linked to/fed from another workbook with cells that have either an R, A or G in - nothing else)
And mainly this is working but on one set of cells it returns nothing as there are 2 As and 2 Gs - what is really annoying is that on a set of cells on a another section of the tab there are 5 A's and 5 G's and that returns an A.
I have a layout something like the following:
A1
A2
A3
[Code]....
Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).
Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".
Ultimately I am looking to have a matrix which contains all the distances between each location:
A1
A2
A3
B1
[Code].....
The function is to show the next workday date in cell a1
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.
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 RelatedI 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?
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.
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 RelatedIm 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 RelatedI 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.
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.
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.
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.
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])"
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).
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.
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
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.
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).
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,
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.
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.)
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.
I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.
I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.
However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)
B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.
Range
B1 C1 D1 E1 F1 G1
2 7 19 45 22 13
H1 Total of matching numbers in cell range K1:P11
I have a list of clients that have specific requests waiting to be actioned, with a number of columns relating to client details and the status of the request across the top of the sheet. One of the columns (D) is "Last Contact Date", where I enter in the date that I last followed up with the client or made contact regarding their request. In the next column (E), I want the weekday/workday date 1 month after the Last Contact Date.
I can use "=D2+DAY(30)" to give me the date 30 days later, or "=DATE(YEAR(D2),MONTH(D2)+1,DAY(D2))" to get one month later, however what I want is the nearest WORKDAY after this date.
So, if the date is on a weekend, I need the Monday date instead.