I'm struggling with the VBA DATEDIFF function. I have a person's DOB and am attempting to determine their age by comparing to a different date. It seems like their age only changes when I change the year, rather than choose a date within the year past their birth date... Here is my code:
Sub Test_click()
DOB1 = #7/19/1930# 'DOB
date2 = #7/18/2012#
Date3 = #7/21/2012#
age1 = DateDiff("yyyy", DOB1, date2) 'age should be 81 (but is 82)
age2 = DateDiff("yyyy", DOB1, date2) 'age is 82 (correct)
End Sub
trying to create a 'countdown' to time of macro completion (based on a static performance time of 1:48). What I want to do is compare a time (ProgTime) set as static at the beginning of the code with 'TIME' and return the difference in seconds. I use the following code:
[Code] ....
When I step through, I can see that ProgTime and Time have different values and yet 'Test' always returns a value of '0'. I tried using 'Now' instead of 'Time' and the same result ('0') occurs. I have experimented with declaring 'Test' as an integer, a long and a date and the result is always the same! I've tried using 'Seconds() around the datediff to ensure an answer in seconds, I've tried 'format' around it with multiple decimal places to make sure I'm not missing a tiny value: Nothing seems to do the trick!
I am attempting to create a time chart that keep track how long a particular person works on an individual project to finally calculate profit & loss. Attached is the spreadsheet.
I am using invoice #'s to differentiate the jobs but have no clue how to make it calculate how much time was spent per job, per person then calculate the total cost per job per person?
sheets.time chart.xlsx
2014 tab- grey column is installer and white is shop employees profit loss tab- all calculations done here payroll tab- all cost data here
I have attached a worksheet that shows the last and first name of an employee along with their hire date and term date, if termed. What I am looking for is a function that will tell me if a person was active as of 12/31/08 which will read true on the side of their name under active; I also need a function that will tell me if a person worked over 6 months in 2008.
The first person John Doe was hired in 2005 and has a true on the side of his name which meant that he was active as of 12/31/08 but as you can see Jane Doe has true on the side of her hire date and false on the side of her term date and I don't want it to read the first true but the last true of someones name, but Jane Doe qualifies for working over 6 months even though she started in 2007.
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.
I have a range of data and need to summarize some of the data. In the table below is an example of the data. I would like to have a single formula where I can count the number of rows where the end date - start date is < 14 days. I would prefer not to add an additional column for the datediff value. Is there a way to do this with either arrays or named ranges?
Name Start End Other data Verizon 1/10/2010 1/25/2010 AT&T 3/2/2010 4/14/2010
I have a workbook that calculates a RAND function based on input in another cell. Specifically, the formula in E2 selects a random text string from another range depending on data entered in D2. I want to be able to fill this formula down for use multiple times.
The formula works fine on its own, but the problem comes when I, say, move on to D3 to input more data. E3 does its random calculation as it should, but E2 also recalculates; I want E2 to remain static once it calculates the first time.
I'm attaching the workbook in question. The calculation I'm referring to is in the 3rd worksheet.
I have a spreadsheet that keeps attendance. Now I need to calculate the units (1 unit= 15 minutes) the children are actual here. On a normal schedule they are here for 16 units but if the are late the units get subtracted. When entering the data, they would like to use letter instead of numbers.
I am trying to calculate payroll. I have 2 columns where regular hours and overtime hours are manually inputed. They are then multiplied by their respective pay per hour columns to come up with regular and overtime pay. The next column adds these to get total gross pay. That gross pay is then multiplied by the FICA and FICA Med factors to figure those taxes(2 different columns). I then have a column the adds all deductions to get total deductions(Fed,FICA,FICAMed,St). The last column subtracts total deductions from the gross pay column to get net income. My problem is the net pay column is $.01 off sometimes.
I think what is happening is I obviously have all columns in dollar amounts with 2 decimal points. Some function columns have multiple decimal points in the answer and then are only showing the 2 decimal points. When those columns are used in the next equation, instead of using the dollar amount that is showing with 2 decimal points, it is remembering the multiple decimal points. This is resulting in being a penny off when I get to the end. How can I get the equations to use what actually shows in the columns(2 decimal points) instead of remembering multiple decimal places?
I have written the code below. But it didn't work. In my opinion the error is most probably caused by irr worksheet function. Because the code is actually bigger and I tried it all part by part.
Option Base 1 Dim i As Integer Dim j As Integer Dim counter As Integer Sub General() 'Makes general calculations Dim Cash_Flow_Project_Entity(49) As Variant 'Toplam Yatýrým Nakit Akýmý Dim FIRR(49) As Variant 'Yearly FIRR Analysis_Period = Worksheets("Veri"). Cells(15, 5) 'Takes " analysis period" from "Veri" Worksheet End Sub
I am making an excel sheet which can track the hypothetical effect building an order has on current inventory. The user is to change the quantities in this hypothetical order whenever they want.
It is composed of two sheets, the inventory sheet and the model sheet. The inventory sheet has the costs of every part carried and how many are currently in stock. The model sheet has a breakdown of what parts are required to build the different models.
I am looking for a function or VBA which can take a hypothetical order (build xx of model one + build xx of model two + build xx of model three), see how many total parts are needed, then calculate and display the effect it has on current inventory. This is only to see the hypothetical effect on inventory, not to actually change the inventory in the inventory sheet.
I've attached what I have so far - most everything is completed except for this final calculation. I have tried using the VLOOKUP function in VBA to try and come up with a solution but I couldn't figure out the best way to go about this. I think the hardest part might be that a hypothetical order can be made up of all three models.
I'm trying to calculate the mode for a large data set, but there is no 'mode function' in subtotal. I need to find the mode for each change in day - without having to retype the function.
I'm writing a spreadsheet where I need to calculate a nested IF function (I think), and I am unsure of how to do it.
The problem is this: The total $ value of the quote is entered, then depending on two variables, a rebate may be deducted from this total value. The first variable is eligibility (a Y/N response), the second is the type of project - there are two types of project and each attract a different rebate amount.
So I have:
A: total quote $ B: Eligibility - 'Y' or 'N' (if 'Y', then value of 'C' is deducted from 'A'), (if 'N', then no value is deducted from 'A') C: Type - '1' or '2' (if '1', then 'C' = $1000), (if '2', then 'C' = $1600) D: final result
I have just built a spreadsheet to calculate training hours amongst other things. I have used the function NETWORKDAYS which calculates the number of working days between two dates. This works fine on my laptop which has the analysis tools pack installed. Unfortunately my work place IS policy wont allow the analysis pack to be installed so need to find away around this. Does anyone know a formula that will perform the same function as the NETWORKDAYS. THis is crucial to the accurate calculation of training hours.
I have a dataset of students' grades (see sample below) which I need to compute the aggregates for.
Blanks indicate that the student did not take the subject. Aggregate is computed based on 6 relevant subjects, lowest is best.
Rules for computing aggregates: 1st subject: EL or HMT 2nd subject: H1 or H2 3rd subject: M1, M2, S1 or S2 4th subject: H1, H2, M1, M2, S1 or S2 (but not previously used) 5th & 6th subjects: Any other 2 not previously used
I'm guessing this is too complicated to be done in a function, but have no idea how to implement this in VBA.
I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way:
Try this formula. .....
Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....
I have a sheet that tracks when people are paid. Each time a person is paid, another entry goes into the sheet with his/her name, pay amount, and date they were paid. So, a given person could have up to 50 lines devoted to them, each with a different date associated with it (the list is sorted by name).
What I need to do is figure out the most recent pay date for each person in the sheet. I would prefer to avoid vba, but experimenting with native excel functions has gotten me nowhere...
I currently have in my equation the trend function to linear extrapolate the date a line would cross either my upper or lower limits (100.1 and 19.9) between 2 data points. However if the 2 data points span both the upper and lower limits (as per the 2 attached examples) then unfortunately the trend function is only able to return the 1 date (where I've specified the x value). Is there any way for the TREND function to trend the 2 values? Do I simply add the 2nd x value to the new x's?
I have attached both the data and their graphs to visually explain my problem : EF Workbook.xlsx
What would the formula be to calculate that array of values = 57% because 4 of them are equal to or greater than 9, and there are 7 of them in total? The formula should accommodate however many numbers are listed and calculate what percentage of them are 9 or 10.
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank =IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)
The last day of the month function is shown below =DATE(YEAR(A13),MONTH(A13)+1,0)
This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.
I want to obtain from some elements the number of cells it takes to appear: We have for example A,B,C, and D, and they appear in the next order:
1A 2C 3D 4A 5D 6B 7C 8A 9A
What I want to know is how much last in appear each element.
1A1 2C2 3D3 4A3 5D2 6B6 7C5 8A4 9A1
For example, the first “A” last one in appear, but the next element “C” last two in appear. In the forth line again cames the “A”, then are three cells. The “C” was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two “A”, then in the cell nine takes one cell in appear again.
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 have a worksheet with some names in a column with a persons first name then the second name and I would like to place the names in a different column I can have this to work ok with the following formula
=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))
This works ok but I have people with three names like Peter May Jones
When I have changed the name I would like to have (Jones, Peter May) but I get (May Peter Jones)
Also the colon I have after the first name gets put at the end of the name not after the last name.
