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
ADVERTISEMENT
Jul 31, 2008
The function is to show the next workday date in cell a1
View 15 Replies
View Related
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
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
May 28, 2012
I've been learnig VB applying it to automating some excel workbooks. What I'd like to know is if there's an equivalent funtion in VB to the IN function in Pascal. The In function in Pascal used to work like this:
If I used the KeyPress (Function in Pascal), I could evaluate which key was pressed and compare it to an array of options. For Example:
HTML Code:
if Chr(KeyPressed) IN ["0".."9",".","+","-","*","/"] then
Expression = Expression + Chr(KeyPressed)
Instead of Comparing each option separadtly. Now my question is whether or not there's an equivalent function in VB.
View 6 Replies
View Related
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
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
Mar 8, 2013
When I need to return numerical data with multiple criteria, I use the SUMIFS and it works fine. But when I need to return a text string, the function is not called for. What function can I use to make this happen? I've tried SUMPRODUCT, which I've had success with in the past, but it's not working this time. Maybe I'm laying it out wrong, but this is how I currently have it:
=SUMPRODUCT(column of text to be returned,((column of criteria1)=criteria1)*((column of criteria2)=criteria2))
Do I need to fix this formula or use something else?
View 2 Replies
View Related
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
Mar 9, 2014
I need the equivalent of a double conditional in the Function SUMIF.
In my example (a test file is attached), I have used hypothetical stock trades as a test case.
Column B has the type of order (Buy or Sell).
Column C has the stock trading symbol.
Column F has the net amount of the transaction on that line.
I want to populate Columns I & J, where I & J are the total Buy and Sell cost for each stock listed in Column H.
So, for example I need to calculate the total "Net Amount" of "Buy" transactions for Stock ABC, and put it in Cell I2. I also need to put the the total "Net Amount" of "Sell" transactions for Stock ABC, and put it in Cell J2.
I'm sure that looking at the attached sample EXCEL workbook, will make it clearer than all my verbiage.
So, conceptually, I need to evaluate Columns B, C and F and put the sum of transactions in Cell I2 for those cases with "Buy" in Column B AND "ABC" in Column C.
If there is no syntax for a double conditional in SUMIF, then I'd be happy with any formula for Cell I2 that accomplished the tax.
I'm not terribly familiar with array formulas, but have used them on occasion if that's what's required to emulate a double conditional in SUMIF.
View 9 Replies
View Related
Dec 1, 2008
I have a spreadsheet that I'm working on that compiles survey data from an online survey. I have averages, high scores, low scores, etc. figuring off of my data to product charts and graphs for a client. I am attempting to find the high and low scores for individual surveys (there are 1054 surveys total right now). I know of max and min, but I really need something that would function like a "maxif" (which I realize does not exist).
Here's the problem:
In column A, I have a list of insurance provider names per survey (so, 1054 entries total). I then have some columns in between that show scores on questions from the survey, and then column AB contains my averages for each individual survey. Column AD has a list of the insurance provider names in alphabetical order (there are 167 unique provider names used in the 1054 surveys). Column AF is my high score column and lines up with column AD (so, I am looking to have 167 high scores in total as I want the high score per each provider, not survey). Let's pretend A2:A10 say "Anthem Insurance." I want to create a formula that would basically say something like:
Count what rows in column A = "Anthem Insurance," then match those cells to AB (so, it would figure since A2:A10 are the targeted cells, then I also want to correspond to AB2:AB10) and give me the high score (MAX) for that range of cells. Is there a way to do this? Otherwise, I have to manually go through all 1054 rows and see what range of cells equal a certain insurance provider name to get those 167 high scores. I can keep doing this if I have to, but this data changes every single week and it eats up a lot of time.
View 4 Replies
View Related
Jun 11, 2008
I am trying to add four hours to a time. That I know how to do, but there is some added logic.
If I put a value in the first column I want to add logic to make the values in second column appear. It is basically adding four hours to a time value unless it is after 5pm, before 8am, etc. This partially works, but doesn't count for the 2pms, 3pms, 4pms, etc.
=IF(OR(G16>0.708333333333333,G16
View 9 Replies
View Related
Jun 27, 2014
As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.
Nights, holidays and holiday night have different price/hour.
The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…
On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!
Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)
View 1 Replies
View Related
Mar 27, 2007
I'm trying to do is to convert an hours per week field as a decimal into hh:mm as text. For example, 22.8 would convert to 22:48.
Public Function DecToHours(Ldecimal1 As Long)
Dim Ldecimal2 As Long
Dim Shours As String
Ldecimal1 = ActiveCell - Round(ActiveCell, 0)
Ldecimal2 = (ActiveCell - Ldecimal1) * 60
Shours = Round(ActiveCell, 0) & ":" & Ldecimal2
End Function
View 5 Replies
View Related
Sep 30, 2008
I'v been trying to do this for a while now. I need a function that takes this date (which is not a set format in Excel, so has to be 'general'):
10.10.2007 00:00
10.10.2007 06:00
10.10.2007 12:00
10.10.2007 18:00
11.11.2007 00:00
...
...
...etc
I need a function that increments this type of date format in a column.
View 5 Replies
View Related
Jan 25, 2008
I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings:
Cell A - Name
Cell B - Monday
Cell C - Tuesday
Cell D - Wednesday
etc
Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem.
The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.
View 5 Replies
View Related
Nov 4, 2008
In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time.
Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.
View 9 Replies
View Related
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
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
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
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
May 19, 2009
I've got a long formula here. If the resulting expression is equal to "0-0" I want it to go blank as if it was an error, and if it isn't, I want it to show the resulting expression as normal.
View 11 Replies
View Related
Dec 19, 2008
I'm trying to account for the date and have it change if the original falls on a weekend. I wrote it using the Weekday function, which I believe is a worksheet function and not a VBA one, as I keep getting a run-time error 5 (invalid procedure, call, or argument). Either that or I have something programmed wrong in it.
View 8 Replies
View Related
Aug 27, 2008
if there is an eomonth() equivalent in VBA?
View 9 Replies
View Related
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
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
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
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.
View 9 Replies
View Related
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
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
