calculate date of birth with an end date and age
I have a sheet with a retirement date which will be the x birthday, and the
age (x) of the client at that date. I need to get a date of birth for each
of these people.
Anyone know a formula that will achieve this??
Worker Retires Age DOB
Fred 15-Mar-10 65 15-Mar-45
"Fred" is in A2, retirement date in B2, age in C2, DOB in D2
In D2: =DATE(YEAR(B2)-C2,MONTH(B2),DAY(B2))
This assume the retirement date exactly corresponds (month & day) to Fred's
I have used a dd-mmm-yy date format to save confusion (international vs USA)
but the dat...apply the formula sign
I=B4ve imported data from other application and some cells have some
formulas like 10*12 but without the =3D sign. How can I apply the =3D sign
to all the cells quickly without going one by one so that it
calculates the formula. Example, =3D10*12, should be 120.
Copy this UDF to a general module in your workbook.
Function EvalCell(RefCell As String)
EvalCell = Evaluate(RefCell)
In an adjacent cell enter =EvalCell(cellref)
Gord Dibben MS Excel MVP
On Thu, 28 Jan 2010 07:12:14 -0800 (PST), canvas <spyele123@g...Audit Trails
I have noticed that when using "Audit Trails", you cannot add tables with
text columns (notes fields) into the Auditable tables section of the Audit
Trail Maintenance screen.
How can i get around this problem? I would have thought that all tables
could be made auditable?
...Look up data from a table in a text box
Hello, I have a text box on a form and I would like data to be viewed from a
table. In the properties of the text box on the control source I place
=[Date Ran]![DT_TM_RAN]. I thought this would show the data in the DT_TM_RAN
field in the text box. But when I open the form it shows
Can someone please direct me in the right direction to fix this issue. Thanks
for your time.
Message posted via AccessMonster.com
I think the Dlookup function is what you want. Help on that function should
tell you e...Creating formula for timeline
I am trying to find a formula in Excel that will help me manage the following
report: I have 2 columns...the first gives a list of months...ex. Jan, Feb,
March, etc. I want a formula that will automatically populate the second
column with a month that is 3 months past the first column...ex. 1st column
is Jan 2nd is April. 1st column is Feb 2nd is May... Is this possible. I
don't want specific dates in the month...just the month itself.
This should do the trick for you.
'Borrowed' from a post a month or so back
Remember they are text so no date calculations can be...MATCH and OFFSET with dynamic range
I would like to do the following with excel Formula.
these are the information
there are already 2 range define in sheet 2 rngDate, rgnValues
How to obtain something like this with Excel formula and not VBA
Jun Dec tot
2003 52 53 105
2002 23 25 48
2001 24 24
I tried to use Match and Offset (but I have a problem with the
Any help will be really apreciate :D
I noticed that ...Converting Access 2000 table to Access 97
How do you link an Access 2000 table to an Access 97
database? Thanks for your time.
...If statement...copy values of cells on another worksheet.
I need a formula that looks at a drop down list on a different worksheet and
if it is "Sold", then the data values from T2:AC2 would show up on the other
worksheet. Is this possible?
I figured it out.
> I need a formula that looks at a drop down list on a different worksheet and
> if it is "Sold", then the data values from T2:AC2 would show up on the other
> worksheet. Is this possible?
I still need help. I need it to look at S2:S86 (drop down list), if it is
"Sold", then T2. I beleive I have to write a formula for e...Copying tables with hidden cells
I have a table in Excel 2000 with hidden columns and I
want to copy it to Word without the hidden columns
reappearing. Is there anyway I can do this?
Not to worry. I've worked out how to do it eventually.
>I have a table in Excel 2000 with hidden columns and I
>want to copy it to Word without the hidden columns
>reappearing. Is there anyway I can do this?
...Copy formula between workbooks
I have a number of workbooks which are identical in form. Each uses
multiple sheets. If a make a formula change in one and then copy the
formula to a different workbook the new formula references the original
workbook and sheet. How can I turn off this "feature" so that the copied
formula will not make any reference to the workbook, only the sheet?
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
To enter the same formula i...Paste special function lacks possibility for copying formulas in '
When creating worksheets, you often need to copy a specific function from one
area to another in the same worksheet (or to another), without changing the
original formulas. This is not possible as a 'paste special funtion'.
When copying formulas in 'paste special' mode, the relative references in
the formula are still changed. In short, when copying the 'formula' =A25' to
another cell, the formula in the copy to cell should still be '=A25'.
Suggestions of changing the orginating formula from relative to absolute
reference (e.g. $A$25) before copying ...IF statement to change font?
Is there an IF statement or other way to change the appearance of data? I
would like the font color to change if an output goes over a said amount.
As an example if we had an hours total out put and wanted it to change to
red when over 40.
Thanks in advance,
You can use conditional formatting to do this:
Assume the cell of interest is cell A1
If you're using Excel 2007
Select cell A1
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
...calculating by color
Is there a way to calculate various total depending on the color of the data entered
scenario: row of mixed numerical data. there is no pattern for the data. at the end of the row there are four cells that need totals to be placed in them from the cells in the row with certain colors. take for example a time sheet for a job...one color for sick days, another for vacation, holidays, etc...is it possible?
See my reply a couple of days ago at
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email ad...Does nyone have a list of Statement Variables
I am trying to add quantities and unit prices to the items listed under the
I have been attempting to do the same task without much luck, I am currently
working with crystal reports to rewrite the AR statement.
It is a pain to have to rewrite the report just to add Item quantity and
unit price, but MS support tells me the built in AR functions wont do it.
Have you had any luck?
> I am trying to add quantities and unit prices to the items listed under the
> account activity.
On ...-- in formula
I got a formula from this group recently that looked like
What does the -- do in this function? MS help was not very
helpful on this.
The -- is double negation. If you do that to a number, it effectively does
nothing ( a positive number is made negative, then positive, and vice
versa). But if you do it to text, it forces excel to convert it to a number
(if the text is a number, like 1, or 2, etc.). If you do it to boolean
values (TRUE or FALSE), it coerces them to 1 and 0. That's what applies
In this case, MONTH(...% Formulas
Does anyone know how to translate a value (�) into a percentage of a
larger value (�).
Any help would be great!
This will yield 0.1. Format for % (% button on formatting toolbar or
Format - Cells - Number - Percent)
mvpearl omitthisword at verizon period net
"Anna" <email@example.com> wrote in message
> Does anyone know how to translate a value (�) into a percentage of a
> larger value...Format only parts of a formula result?
I created a formula composed mostly of text, including a small formula.
="The products would be $"&ROUND(E28/1000,0)&"K but since new revenues
are enabled almost instantly,"
is intended to produce:
The products would be $1335K but since new revenues are enabled almost
Is there a way to BOLD or ITALICIZE the "$1335K" result but leave the
other text unbolded?
sdubose99's Profile: http://www.excelforum.com/member.php?action...Date Formula Needed-Business Days
I need a formula that calculates business days, but the NETWORKDAYS function
is not adequate. I need the formula to return the number of days between two
dates, excluding a range of holidays. Since my store is open seven days a
week, the NETWORKDAYS function will not suffice. When I used Quattro Pro in
the past, it had a BDAY function, and it accepted parameters to indicate if
weedends were to be included or not. Is there a comparable function in Excel?
I am trying to calculate the number of days the store is open every week. I
thought of using NETWORKDAYS and simply adding 2 (Satu...Access 2000 IIF statement
I am having problems trying to figure out how to write a statement that only
shows a column if there is something in the column. Right now I get a bunch
of blank records and then the one record with the stuff in it that I need.
I have a report that works off a query. The query works off a table and has
a field in it called "other_new_specify" and one with
"other_pending_specify". What I want to do is have the field show up on the
report only when there is something populating it those two fields.
The following statement gives me errors
Iif(IsNull(other_pendi...How to get a ChartTitle formula
With an object ChartTitle, one can assign to the property Text a reference
to a cell:
ChartTitle.Text = "=Sheet1!$C$3"
What I would like to know is if there is a way to read this reference from
the object ChartTitle, because all I can see is the result of the formula
and not the formula itself.
...calculating time formula
i want to set up a time summation calculation in excel.
for example i put in how many hours i've working on something over a number
of days down one column and a "Total hours and mins worked" cell somewhere
else just adds all my inputs in hour and min format. eg. 03:20 (ie. 3 hours
- thanks for any comments.
just enter your time values in Ex^cel's time format and use a formula
Important: Format your target cell with the custom format [hh]:mm
"Nick" <firstname.lastname@example.org> schrieb im ...Formula and field types for calculating times
I don't get it how to format fields and apply some formula to calculate
I want the difference of two time fields expressed as
12:30 - 08:15 = 4.25 (4 hours and 15 minutes)
and format as General
Gary''s Student - gsnu2007d
To learn all about calculating with time, visit Chip Pearson's site:
Microsoft MVP - Excel
"Hans-Martin Burger" <MartHof@gmx.de> wrote in message ne...Search and replace formula?
I have a selection of data in this typical format:
I want to be able to replace all instances where a letter is followe
by a '$' sign to a constant different letter for example 'e$'.
Is there a formula I can use that can search and replace to give me th
fastf...pivot table #18
I have an excel file created daily which keeps 3 columns
data. What I need is: each time the users open it they
should see data in a pivot table, not as three-columns.
Can I do that? The file is ready only and user does not
have to write it back.
Couldn't you create the pivottable the same time that the workbook is created.
If no, then maybe you can record a macro that creates the pivottable once.
Then either run this macro after the creation--or give the macro to the
users...In fact, maybe you could give the users a macro that would open the
newly created file a...Pivot Table Data Filter Problem
I have created a pivot table with people's names in the ROW, year group
and academic year in the COLUMN and weight/height in the DATA area.
Below is a copy of the pivot table...
Year Group Academic Year
NAME Data 2004 2005
NAME 1 Height 1.44 1.44
Weight 51.00 51.00
NAME 2 Height 1.44 1.44
Weight 51.00 51.00
The DATA field has a drop down box to filter weight/height but if I use
the filter to only show Height, I cannot get weight back (unless I
undo). Why does the data column not have the same functionality (ie;
show all, filter, then go back to show all) as, say, the NAME colu...