I am looking for a solution to my problem in access in regards to a calculation that shows the days from the beggining of a project to a specific date. For example: a project could have a start date of 01/01/2001, I want to figure out how many days has this project been worked on as of 03/22/2007. I need this to show as of a past date, therefore now() doesn't work. In excel the formula is Date(year,month,day), but when I put that in access it doesn't work. Can somebody help me? Hopefully you can. -- Nick Nick The Access function Date() returns the current date on your PC. If...

I have a date, (6/11/04 for example) and I want to calculate a new dat plus or minus a certain number of months from my known date. So th calculated field would be my known date minus six months. How do I d this? Keit -- Message posted from http://www.ExcelForum.com Hi Keith! As far as a general solution is concerned, I'd base this on the following: =DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+AddMons+1,0)))) Where AddMons is the number of months to be added or subtracted. Peter Dorigo, produced a more efficient form: =MIN(DATE(YEAR(A1),MONTH(A1)+...

I'm trying to calculate age using the date of birth of our customers minus todays date but am coming up with another date rather than their age. Is there a way of doing this? You can use the DATEDIF function to do this. See www.cpearson.com/excel/datedif.htm for more details. -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com chip@cpearson.com "acarbonara" <acarbonara@niagaracu.com> wrote in message news:0f1701c38da7$e0720710$a301280a@phx.gbl... > I'm trying to calculate age using the date of birth of our > customers minus todays date bu...

1. how do I code to prompt just to enter one date? 2. Once this date is entered how do I code it to subract 20 days from that date entered. 3.Next, I have another date field that I do not want data pulled greater than 45 days out. I want to work like this: Employee enters date..and the querie will pull on field all data greater than 20 days, but another field no <= 45 days You need a parameter in the query. This parameter must be defined. The SQL statement would look something like this with the correct table and field names. PARAMETERS [Enter Date] DateTime; SELECT t...

I have a database with names (a1) dates(d1) months (e1) and dates (g1) (other colums are irrelevant like job descriptions) We have several daily events and I need to work out calculation of who attands what % of daily and monthly events. How would I do this? -- Meggen ------------------------------------------------------------------------ Meggen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24942 View this thread: http://www.excelforum.com/showthread.php?threadid=384793 Do you have information on who attends these events in your "database"? I...

Hi, I have several different forms using subforms in continuous form view. Some are calculated controls. I seem to have problems with Access in continual 'Calculating...' mode, with the form essentially locked up. Pressing F9 stops this, and all is well and works fine. As best I can trace it, it is because of the calculated fields, but I can't see any circulating formulas. For example, =nz([UnitsOuterLast])*nz([LastOuterStock])+nz([LastUnitStock]) locks up one form as stated. All three parts are fields, not even referencing other calculated controls (Or vice versa). If I...

I have a text string in a cell that comes from an AS400. I need for this string to convert to a time value. Example text: 20100409073000 In the AS400 this represents the date of 4/9/2010 and 7:30 AM. So YYYYMMDDHHMMSS is the format. What I need to do is extract the 073000 part off and convert it to an actual time, so it can then be compared to the system time using the Now function to calculate the time elasped. The time elapsed would need to show in number format, so 4 hours and 15 minutes would be 4.25 Any help would be appreciated. -- Thanks Try =(MOD(NO...

I am trying to maintain an average of 35 in Sunday School. Each week I input the number of attendees in my excel file. I need a formula that will tell me how many I need next Sunday, to make my year long goal of 35 as an average. For Ex: A B C 1 Date # of Attendees #needed next week to avg 35 2 1/3/10 34 (? Formula so #36 would show) 3 1/10/10 38 (? Formula so #34 would show) 4. Etc. Etc. Etc. Please help! Submitted via EggHeadCafe - Software Developer Portal of Choice Using the W...

...

I want to take data from one spreadsheet to make a ranked table (soccer league). Is it possible to design it so that the league will automatically update (and re-rank the teams and the data) when I enter new data in the spreadsheet? -- Spobber ------------------------------------------------------------------------ Spobber's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19966 View this thread: http://www.excelforum.com/showthread.php?threadid=345736 ...

My workshhet displays the formula that I entered (Vlookup) and is not displaying the values of the function. Formatted cells to numbers, copied formula from a worksheet that dispalys values etc etc but nothing is working. How could I resolve this? Sounds like an Excel question and this news group is for Access. Try putting an equal sign in front of the formula. -- KARL DEWEY Build a little - Test a little "Ann" wrote: > My workshhet displays the formula that I entered (Vlookup) and is not > displaying the values of the function. > > Formatted cells to numbers...

I'm a novice to Excel and using Excel 2002 ver. I need to keep track of employee absences for my company in a "rolling" 12 month period. e.g. if an individual is permitted "x" days off/year and absent from work more or less than the allocation -what would the formula be to determine an available total on any day of the year. Thanks. It depends on what your data looks like. Tell us about your data structure. Regards, Fred. "Suede" <Suede@discussions.microsoft.com> wrote in message news:55720EBC-C070-43B5-A699-375153F4E154@microsoft...

I have a simple query Name and Date of Birth. I want to calculate the age in a query. I have tried different criteria from MS Access Tutorials. Can someone please help me with a correct criteria to calculate the contacts age as of today from the date of birth. Thanks, Deeds37 Deeds37, Without seeing what you tried... can you tell me why the below didn't work for you? =DateDiff("yyyy", [YourBirthDateField], Now())+ Int( Format(Now(), "mmdd") < Format( [YourBirthDateField], "mmdd") ) -- Gina Whipp 2010 Microsoft MVP (Access) ...

Is it possible to sum the numbers in one column based on a date criteria in another column e.g. I need to count the number of requests received within a week of todays date, so in this case it would be 1 25/05/2004 (=today() No of requests Date request sen 12 24/05/200 1 12/05/200 24 23/04/200 5 20/05/200 I have tried a sumif, where I used the criteria =SUMIF(B:B,">=A1-7",A4:A7) - where column B is the date request sent, A1 is todays date (us...

I have the following formula and it returns the wrong calculation K1 =.03 K2 =.02 In this situation, its supposed to multiply F82*K2, instead it is multiplying F82* K1 =IF(E82="service a",F82*$K$1,IF(AND(E82="Service B",MATCH(C82,'Sheet 2!$C$2:$C$15)),F82*$K$1,F82*$K$2)) any help is appreciated. To simplify your formula try this: =IF(OR(E82="service a",AND(E82="Service B",MATCH(C82,'Sheet 2!$C$2:$C$15))),F82*$K$1,F82*$K$2) -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://w...

I have a table that is part of a Word 2007 template. It has automatic calculations set up where Column A times Column B is equal to a total in Column C. Everything is in currency format (US dollars). How can I get the totals to format with no zeros after the decimal point when necessary and yet have denominations in cents when necessary? Right now the totals can look something like this: $2,500.00 $1,140.50 $950.50 $875.00 I want there to be no ".00" so it would look like this: $2,500 $1,140.50 $950.50 $875 Just to throw a wrench in things, it would be t...

I have a question about the Currency Calculator and a potential bug report: Can the order of the types of currency in the calculator be changed? They are labeled with letters, which makes me hopeful that they can. POSSIBLE BUG REPORT The Calculator reports $1 for the value of ONE roll of pennies. Now, unless penny rolls are different up in Redmond (home of Microsoft), they are $.50. WHAT GIVES? Can this value be changed by me? PLEASE, CC me on responses to the newsgroup. THANKS! ______________________________________________________ Larry Leveen OlyBikes Bikes, Parts, Repairs &...

Hi Can a defined 'Name' be used in a calculated field? You can't use names, or cell references in a calculated field. nc wrote: > Hi > > Can a defined 'Name' be used in a calculated field? > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

I use iterated calculations often in my worksheets. I keep turning the option ON but it keeps turning itself OFF so I get a error/warning message every time a open a sheet with circular references. How can I make the option stay ON? Thanks Nick Nick, When a workbook is stored, the state of Iteration (on/off) is stored with that workbook. When the first workbook is opened, Iteration is set to that stored in that workbook. Any additional workbooks opened do not change the state of Iteration. It remains as set by the first workbook. -- Earl Kiosterud mvpearl omitthisword at verizo...

I'm doing a timesheet. But I have some problems. the only formul missing is if somebody works from 9am to 6pm for a total of 9hours o work "=(C3<B3)+C3-B3" then I want to remove an hour of lunch on th total time work if that person as work more than 6 hours. Exemple. If the persone work a total of 6 hours, then remove 3 minutes. if the person work more than 6, then remove 1 hour -- Message posted from http://www.ExcelForum.com One way: I'll suggest using this equivalent to your formula: =MOD(C3-B3,1) just for brevity. Since XL stores times as fractional day...

To my excel-2003 pivot table report I am unable to add a - Calculated Field - as the menu item "Calculated Item" is not found on "Pivot Table Tool Bar", "Formulas". I found the "Excel Tips and Techniques" is also do not have any reference to this subject. Is Calculated Item not on the menu at all, or is it dimmed out, and not available to use? If it's visible, but dimmed out, try selecting a heading cell in the pivot table's column or row area. Then choose PivotTable>Formulas>Calculated Item. Jacob wrote: > To my excel-2003 pivo...

Hi Excel Gurus, How do I calculate a value on a new cell based on what the user selects from a List. For Example : If I have a dropdown List of 5 items -> APPLE, ORANGE, BANANA, GRAPES, PINEAPPLE Selecting APPLE should paste a value 1 in the adjoining cell Selecting ORANGE should paste a value 2 in the adjoining cell Selecting BANANA should paste a value 3 in the adjoining cell Selecting GRAPES should paste a value 4 in the adjoining cell Selecting PINEAPPLE should paste a value 5 in the adjoining cell Thanks for your time. Warm Regards, Ranjit S Hans Hi You can do it this way:...

I have a spreadsheet listing employees jobs in one column. Another column lists if they are full time or part time. There are several employees with the same job but work different times. I need a formula to calculate how many people with that title work full time and how many people with the same job work part time. A pivot table will do a very nice job for you. They are very powerful once you get to know them. Take a look at Chip Pearson's site for a tutorial on them: http://www.cpearson.com/excel/pivots.htm -- Regards, Fred "VP" <VP@discussions.microsoft.com&...

I have an Excel sheet for our "Shipping Manifest". It shows how many pieces in a carton, carton size, cubic feet, etc. We have standard carton sizes we use which we've named. Carton A = 18 x 18 x 18 = 3.38 Cubic Feet Carton B = 18 x 18 x 14 = 2.63 Cubic Feet Carton C = 18 x 16 x 14 = 2.33 Cubic Feet ... etc, on down to Carton J. Is there a way to make it so we can type A, B, C, etc under "CTN SIZE" column it will automatically enter the cubic feet for us under the cubic feet column? Then at the bottom of the sheet we'll have a total which will total up the cu...

I am writing an app in C# to keep details of expenditure in relation to a small block of flats. There are 9 flats and costs are apportioned based on the lease terms, in all about 12 different categories each with a different percentage cost for each flat. I store the amounts and the proportions as decimal numbers with 2 decimal places in an Access database. I am using one central function to do the calculations: public static Decimal CalculateProportion(Decimal amount, Decimal proportion) { double famount = (double)amount; double fproportion = (double)proportion; doub...