Date Specific calculation
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.
The Access function Date() returns the current date on your PC.
If...calculate date by subtracting months
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
Message posted from http://www.ExcelForum.com
As far as a general solution is concerned, I'd base this on the
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)+...Help!! How do I calculate age in an Excel spreadsheet
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.
Microsoft MVP - Excel
"acarbonara" <email@example.com> wrote in message
> I'm trying to calculate age using the date of birth of our
> customers minus todays date bu...date calculation 01-29-10
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
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
PARAMETERS [Enter Date] DateTime;
SELECT t...% calculation
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'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...Calculated controls
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
As best I can trace it, it is because of the calculated fields, but I can't
see any circulating formulas. For example,
locks up one form as stated. All three parts are fields, not even
referencing other calculated controls (Or vice versa). If I...Time elapsed calculation
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.
=(MOD(NO...Average Calculation for Sunday School Year Long Goal
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.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Using the W...What formula do I use to calculate compound annual growth rate (C.
...Automatic updates of a ranked table
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's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19966
View this thread: http://www.excelforum.com/showthread.php?threadid=345736
...Worksheet diplays formulas and not the calculated values.
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.
Build a little - Test a little
> My workshhet displays the formula that I entered (Vlookup) and is not
> displaying the values of the function.
> Formatted cells to numbers...Calculating total number of days in rolling year
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.
It depends on what your data looks like. Tell us about your data structure.
"Suede" <Suede@discussions.microsoft.com> wrote in message
news:55720EBC-C070-43B5-A699-375153F4E154@microsoft...Criteria for age calculation
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.
Without seeing what you tried... can you tell me why the below didn't work
=DateDiff("yyyy", [YourBirthDateField], Now())+ Int( Format(Now(), "mmdd") <
Format( [YourBirthDateField], "mmdd") )
2010 Microsoft MVP (Access)
...calculations within a date range
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
No of requests Date request sen
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...calculation based on meeting two criteria
I have the following formula and it returns the wrong calculation
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
any help is appreciated.
To simplify your formula try this:
=IF(OR(E82="service a",AND(E82="Service B",MATCH(C82,'Sheet
Cutter's Profile: http://w...Calculations problem
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:
I want there to be no ".00" so it would look like this:
Just to throw a wrench in things, it would be t...RMS BUG REPORT & currency calculator question
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.
Bikes, Parts, Repairs
&...Calculated field in pivot tables
Can a defined 'Name' be used in a calculated field?
You can't use names, or cell references in a calculated field.
> Can a defined 'Name' be used in a calculated field?
Excel FAQ, Tips & Book List
...setting iteration calculation options
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?
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.
mvpearl omitthisword at verizo...Excel Calculating Problem
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
I'll suggest using this equivalent to your formula:
just for brevity.
Since XL stores times as fractional day...PivotTable toolbar, Formulas, Calculated Item
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
> To my excel-2003 pivo...Calculate Value on new cell based on List
Hi Excel Gurus,
How do I calculate a value on a new cell based on what the user selects from
For Example :
If I have a dropdown List of 5 items -> APPLE, ORANGE, BANANA, GRAPES,
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.
Ranjit S Hans
You can do it this way:...Formula calculating fulltime/parttime vs employees.
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:
"VP" <VP@discussions.microsoft.com&...Carton size and cubic feet auto calculate..
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...Improving Accuracy Of Decimal Calculations
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
double famount = (double)amount;
double fproportion = (double)proportion;