Fraction and age calculation

Two questions:

1. I am trying to enter a fraction into a cell but it keeps reducing i
to the lowest common denominator.  How do I get it to keep the exac
fraction I've entered?

2. How do I calculate how old a person is, displayed as a single an
whole number, given their date of birth and todays date.  My date o
birth cell is J6 and the age cell is J7.

Thank

--
Message posted from http://www.ExcelForum.com

0
3/7/2004 2:25:36 AM
excel 39879 articles. 2 followers. Follow

7 Replies
613 Views

Similar Articles

[PageSpeed] 13

Hi milesryl!

Try to keep to one question per thread.

Fractions:

Use a custom format to avoid the fraction simplifying:

eg
# ??/12

Will display (eg) 5 6/12

Age:

Assuming you want years:

=DATEDIF(J6,TODAY(),"y")

For details of DATEDIF see:

Chip Pearson:
http://www.cpearson.com/excel/datedif.htm
-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"milesryl >" <<milesryl.12q7sm@excelforum-nospam.com> wrote in message
news:milesryl.12q7sm@excelforum-nospam.com...
> Two questions:
>
> 1. I am trying to enter a fraction into a cell but it keeps reducing
it
> to the lowest common denominator.  How do I get it to keep the exact
> fraction I've entered?
>
> 2. How do I calculate how old a person is, displayed as a single and
> whole number, given their date of birth and todays date.  My date of
> birth cell is J6 and the age cell is J7.
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
njharker (1646)
3/7/2004 2:43:13 AM
Sorry didn't see Normal had already answered, but will still point out
that results from DATEDIF are subject to interpretation.

#1  if you aren't using the fraction numerically you can enter the
      fraction as  text  by preceding it with a single quote.

#2 to show the age of a person see DATEDIF on Chip Pearson's site.
        http://www.cpearson.com/excel/datedif.htm

Example on his site,  but read the web page.  DATEDIF calculates thise numbers
similar to the way you probably would, but differences may occur when dates
include a day of month greater than 28 in the dates you are finding the difference
between.
=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days"
his will return a string like     33 years, 9 months, 18 days
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"milesryl >" <<milesryl.12q7sm@excelforum-nospam.com> wrote in message news:milesryl.12q7sm@excelforum-nospam.com...
> Two questions:
>
> 1. I am trying to enter a fraction into a cell but it keeps reducing it
> to the lowest common denominator.  How do I get it to keep the exact
> fraction I've entered?
>
> 2. How do I calculate how old a person is, displayed as a single and
> whole number, given their date of birth and todays date.  My date of
> birth cell is J6 and the age cell is J7.
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>



0
dmcritchie (2586)
3/7/2004 12:01:14 PM
<<"Sorry didn't see Normal had already answered">>
Some folks don't consider him that!<bg>

(Sorry ... just couldn't resist)<g>
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"David McRitchie" <dmcritchie@msn.com> wrote in message
news:%235V4cwDBEHA.3284@TK2MSFTNGP09.phx.gbl...
> Sorry didn't see Normal had already answered, but will still point out
> that results from DATEDIF are subject to interpretation.
>
> #1  if you aren't using the fraction numerically you can enter the
>       fraction as  text  by preceding it with a single quote.
>
> #2 to show the age of a person see DATEDIF on Chip Pearson's site.
>         http://www.cpearson.com/excel/datedif.htm
>
> Example on his site,  but read the web page.  DATEDIF calculates thise
numbers
> similar to the way you probably would, but differences may occur when
dates
> include a day of month greater than 28 in the dates you are finding the
difference
> between.
> =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, "
& DATEDIF(A1,NOW(),"md") & " days"
> his will return a string like     33 years, 9 months, 18 days
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

0
ragdyer1 (4060)
3/7/2004 4:25:23 PM
Anybody who spends 15 hours a day in the newsgroups seems
Normal to me.

"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message news:%23iJLODGBEHA.3776@tk2msftngp13.phx.gbl...
> <<"Sorry didn't see Normal had already answered">>
> Some folks don't consider him that!<bg>
>
> (Sorry ... just couldn't resist)<g>


0
dmcritchie (2586)
3/7/2004 4:54:16 PM
Hi Gord!

A1:
Some folks don't consider him that!<bg>
A2:
=SUBSTITUTE(SUBSTITUTE(A1,"Some","Most"),"<bg>",":(")

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.


0
njharker (1646)
3/7/2004 11:10:54 PM
Hi RD!

Sorry! Got my target wrong.

-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:%23iJLODGBEHA.3776@tk2msftngp13.phx.gbl...
> <<"Sorry didn't see Normal had already answered">>
> Some folks don't consider him that!<bg>
>
> (Sorry ... just couldn't resist)<g>
> -- 
> Regards,
>
> RD
>
> --------------------------------------------------------------------
-------
> Please keep all correspondence within the NewsGroup, so all may
benefit !
> --------------------------------------------------------------------
-------
> "David McRitchie" <dmcritchie@msn.com> wrote in message
> news:%235V4cwDBEHA.3284@TK2MSFTNGP09.phx.gbl...
> > Sorry didn't see Normal had already answered, but will still point
out
> > that results from DATEDIF are subject to interpretation.
> >
> > #1  if you aren't using the fraction numerically you can enter the
> >       fraction as  text  by preceding it with a single quote.
> >
> > #2 to show the age of a person see DATEDIF on Chip Pearson's site.
> >         http://www.cpearson.com/excel/datedif.htm
> >
> > Example on his site,  but read the web page.  DATEDIF calculates
thise
> numbers
> > similar to the way you probably would, but differences may occur
when
> dates
> > include a day of month greater than 28 in the dates you are
finding the
> difference
> > between.
> > =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
months, "
> & DATEDIF(A1,NOW(),"md") & " days"
> > his will return a string like     33 years, 9 months, 18 days
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel    [site changed  Nov.
2001]
> > My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
>


0
njharker (1646)
3/7/2004 11:11:37 PM
You just proved it AGAIN!<vvvvbg><darfc>
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Norman Harker" <njharker@optusnet.com.au> wrote in message
news:404bac01$0$3953$afc38c87@news.optusnet.com.au...
> Hi Gord!
>
> A1:
> Some folks don't consider him that!<bg>
> A2:
> =SUBSTITUTE(SUBSTITUTE(A1,"Some","Most"),"<bg>",":(")
>
> -- 
> Regards
> Norman Harker MVP (Excel)
> Sydney, Australia
> njharker@optusnet.com.au
> Excel and Word Function Lists (Classifications, Syntax and Arguments)
> available free to good homes.
>
>

0
ragdyer1 (4060)
3/8/2004 12:02:49 AM
Reply:

Similar Artilces:

Diff between A/R aged and historical aged T/B
Can anyone explain the difference between the Aged Trial Balance and Historical Aged Trial Balance reports? From what I understand, the Aged Trial Balance shows the agings as they've last been calculated by running the Receivables Aging Process. How does the Historical Aged T/B come up with its aging? If there's an definition of the two, I sure can't find it in the documentation. Thanks!! Bud, The main difference is that the Aged Trial Balance (ATB) only looks at current tables to give you the AR aging, whereas the Historical Aged Trial Balance (HATB) looks at both the c...

Esgrow Balance Calculation
I need to come up with a formula for calculating the balance in an esgrow account. Given a number of regular draws (taxes, insurance payments) throughout the year, I need to figure out how much needs to be initially deposited in the esgrow account and how much needs to be deposited monthly such that the account value doesn't dip below a specified threshhold value. I also need to be able to separate out the balance for the various components making up the esgrow account so that I know how much is allocated for taxes, insurance payments, etc. ...

Calculation query, how to avoid extreme values?
Good morning, I have 1 table with a few fields (A, B, C) that I'm using for various calculations. For various reasons are some of the input "weird" and I want to leave that out of the calculation (average). For example, Field A requires > 0 AND < 100, B < 400 Now, if one record doesn't fullfil A requirement I don't want to use it for A's average calculation BUT I want to use it for B's average calculation (if it satisfy B's requirement). This means that if I use the query criteria A: >0 AND <100 it will filter out all other records an...

Calculation using rolling 4 week amounts
Folks A colleague has developed a spreadsheet into which he inputs numbers over 4 weeks. These numbers are added together and divided by 4 to give an average amount. this average amount is used in a number of other calculations around the spreadsheet. Is it possible to write a formula which will automatically add the next week's amount, and drop off the previous 4th week? If not, any ideas what the best solution will be? He is currently changing everything manually. Eg weeks might be 31/10/05 07/11/05 14/11/05 21/11/05 how can he automatically drop off 31/10/05 and include...

How do I write expressions to calculate rolling 13 week averages?
If anyone knows how to do this please post answer. Please use the Message space to better describe the problem. The subject is cut off when viewing it. Also things like the table name(s) with the required field names would help us to help you. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Harmeet" wrote: > If anyone knows how to do this please post answer. ...

Year to date expression/calculation
I have a report based on a query that has a number of calculated controls. The query also includes a month as txtmonth which is used in the report. data is collected half yearly in June and December. I am trying to produce a report that looks like this: June 2009 Year to date Field 1 Total 2000 2000 Field 2 Total 1000 1000 Field 3 Total 3000 3000 December 2009 Year to date Field 1 Total 1000...

Hour's pay calculations
I have read a few of the answers in some other posts and I'm almos afraid to ask my questions because I might not understand the response Nevertheless, I am going to try. I am paid by the hour where I work. The hourly pay is not consisten but based on what day and time of day I work. For example; For the sake of the argument (pun intended though probably no understood), let us say the following values apply; $15 an hour from 08:00 to 13:00 $21 from 13:00 - 16:00 $26 from 16:00 $30 for all hours beyond the eight (These values vary from day to day within a week but all weeks have th sam...

Calculate Gestational Age based on months and weeks
Hi, SO I want to make a pregnancy wheel: (LMP(Date) - 3months, +7d)= Estimated Date of Delivery (it also has to advance to the next year if >April. Also, I want to be able to automaticallt update the current Gestational age(#weeks/days since LMP to current date) based on today's date and the LMP. I found this code online at a website that calculates Gestational age in online calculator. Can it be converted for use in access? Thanks! <script> // current equation code function PregDates() { var lmpid = $("#lmp").val(); var ddid = $("#duedate...

Access
Hi I have designed a simple database to collect sales information about manufacturing Kitchens and wardrobes. On my main form (created from my main sales table) I have 2 subforms as follows: Type Qty Price Total 1 2 2000 4000 2 2 4000 8000 3 2 3500 7000 That is the 1st subform I created and the total comes from Qty x Price. The 2nd subform is the same with wardrobe prices - total is the same setup. On my main form I want to total the totals for the first subform and then total the totals from the second subform an...

Calculating total hours
If i have 4 workers who worked for 8:25, and multiply that, (ie, 4x8:25 I should get 33 hours they worked total. But in Excel, I am getting 9:40. What formula do I need to put in a cell to get 33 hours? -- gary6 ----------------------------------------------------------------------- gary66's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1606 View this thread: http://www.excelforum.com/showthread.php?threadid=27763 Hi Gary Excel doing this as 8:25 looks like 8:25am to it, if you just enter 8.25 instead of the 8:25 it will work. If that's not an option th...

Why do I get calculation errors with Athlon and Excel 2003?
I am running a simple calc on a new computer that I bought with an Athlon 3200+ processor. The calc adds either .1 or .2 to the previous result depending on a condition. After the calc runs on the first 15 or so rows, instead of adding a .1 it adds a .100000000000001 which, although small, creates an error and later "IF" statements fail a test that should be true. Does anyone know of a problem with Excel 2003 and the Athlon 64 3200+ processor? Rick1459 "Rick1459" wrote: > The calc adds either .1 or .2 to the previous result depending on a condition. > After ...

aging accounts
Good day! I am monitoring the remittance of about 700 employers remitting health insurance premiums. My task right now is to determine aging accounts. The first thing I did was make a table of months. Second, I made a query showing the months that each employer should have remittance. Then, I made a query asking to show months without remittance. From there I calculated the aging. But I have problems, one, not all of these employers started operating at the same period. For example, Employer A started operating March 2010 so premium contribution will also start on this month. ...

how do I calculate times and get as a result numbers
I got a Spreedsheet that I whant to use as a time sheet, this is so far what I got done wrong what I want to get is right a1= 10:00am time in a1= 10:00am b1= 2:00pm time out (lunch) b1= 2:00pm c1= 2:30pm time in (lunch) c1= 2:30pm d1= 8:00pm time out d1= 8:00pm e1= 8:30 total e1= 8.5 the answer has to be a number if the time is #:0<= #:14 = #.0 #:15<=#:29 = #.25 ...

Alpha Phrase To Phone Number Calculator in excel
Alpha Phrase To Phone Number Calculator. Is there a way to convert text to phone numbers in excel. I need a formula. Like this example : http://www.csgnetwork.com/phonenumcvtrev.html I am sure there are better ways but if you create a list with the letters in a 2X26 table, i.e. A 2 B 2 C 2 D 3 E 3 F 3 G 4 and so on, call the table Tbl then use this formula =LEFT(G1,4)&VLOOKUP(MID(G1,5,1),Tbl,2,0)&VLOOKUP(MID(G1,6,1),Tbl,2,0)&VLOOKU P(MID(G1,6,1),Tbl,2,0)&VLOOKUP(MID(G1,7,1),Tbl,2,0)&VLOOKUP(MID(G1,8,1),Tbl, 2,0)&VLOOKUP(MID(G1,9,1),Tbl,2,0)...

Replication of address book takes ages
Hello, I've noticed that when I create new distribution lists, create new users or make a change to someone email addresses it seems to take days for that to replicate out to the users address book. Even when I select to download the address book within outlook 2003! Is there any reason for this? Thanks in advance Mike D "Mike D" <anonymous@discussions.microsoft.com> wrote: >Hello, I've noticed that when I create new distribution >lists, create new users or make a change to someone email >addresses it seems to take days for that to replicate out >...

Calculate time difference
Hi I keep getting error when calculating time difference between Column E and Column C. I get the error #value. How can I fix this and get actual time difference between two columns. Time is military style. Thank you Column C Column D Column E 10.48.27 28-Feb-10 15.06.11 19.19.12 28-Feb-10 22.40.36 22.07.19 1-Mar-10 05.22.28 05.51.31 1-Mar-10 06.46.35 08.04.07 1-Mar-10 10.18.22 17.59.57 1-Mar-10 20.25.57 22.24.15 2-Mar-10 01.04.16 01.53.59 2-Mar-10 06.04.58 07.30.19 2-Mar-10 14.59.21 15.31.13 2-Mar-10 17.28.39 18.17.19 2-Mar-10 21.38.14 22.11.36 3-Mar-10 05.20.30 07.0...

slow moving calculations between pages
I am working on a file (13.2 MB) with about 20K lines per tab (4 tabs and several vlookups. When I enter new data on my "inputs" page, th related calculations on the next page take 30 sec + to update. Wha can I do to to speed up the file? I've made some attempts to reduc the size, but my efforts seem to be in vain. PLEASE HELP...thanks! Abby: -- Message posted from http://www.ExcelForum.com Hi, go to Tools/Options/Calculations tab. change from automatic to manual calculations. then Excel will only calculate all those relationships when you hit F9. jeff >-----Origin...

credit limit aged
how can i make customer credit limit only for 90 days. after 90 days the customer did not pay the money the cashier can't make invoice for that customer. how can i set this feature in RMS. anyone can help me thanking you dinesh ...

Select records based upon calculated totals
I've created a report that shows the contract price of a project and using a subreport of invoices, a text box to calculate percentage of $ invoiced, which works great. I'd like to now create a report that shows only the projects that have not been invoiced 100%. What about the idea of doing that calculation in the underlying query, then using the query to feed the report? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein do...

I'm a salaried employee, ?how to calculate actual rate of pay
I am on salary (weekly) but always work alot more than 40 hrs per week. How can I make a spreadsheet that will calculate my rate of pay on a weekly basis, by me typing in the hours worked per week(ex; 40 reg + 20 overtime) Please Help, Thanks in advance. Hi Just say your O/T is at double time. Try this formula =(A1-40)*A3+(40*A3/2) with your total hours in A1 and your double time payrate in A3 HTH Michael M "help in CA" wrote: > I am on salary (weekly) but always work alot more than 40 hrs per week. > How can I make a spreadsheet that will calculate my rate of pay ...

Calculations
I am stumped about how to write an expression. fields 1, 2 & 3 are summed in field 4. field 5 is a calculated field to be subtracted from field 4. Field 5 can only bebe equal to or less than field 4. Please help. Patty Where are you trying to do this? Is this an MS Access question? Those kinds of calculations and comparisons work well in a tool designed to do them ... i.e., Excel. More info, please... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or des...

How to eliminate zero values on graphs when formula calculates 0?
I'm setting up a graph of data that is calculated with an "if" function over a series of days of input data including future days. When the input data cell is blank I would like the calculated value to not show a zero value on the graph. If the cell were blank, the plot would not show a zero value. The problem is that at the end of the line graph the line always falls off to zero. =IF(A1="",NA(),your_formula) -- David Biddulph "sjgolfer" <sjgolfer@discussions.microsoft.com> wrote in message news:29E4FF52-DC7D-4419-A5EF-63BAFF93E729@microsoft....

Calculating Percentages in Excel
Hi I have three columns as below Unit Price ABC Company Price EFG Company Price Not Known 40% discount 30% Discount Not Known 20% discount 30% Discount I am getting a little confused because there seems to be in the Excel help file different ways to calculate, and I keep getting different answers! I need to work out firstly what the Unit price is, and I need to work it out from the discount given to EFG Co...

GP calculates new depreciation on a fully depreciated asset
We performed a mass change on a class of assets to change the Class ID and GL account number. After the mass change completed, we ran depreciation for the month, and some of the assets that were included in the mass change that were fully depreciated now show an amount in the Current Depreciation field (flagged as Y for Fully Depreciated and NBV shows as $0). A depreciation amount was also sent to the GL as part of the depreciation batch. We tried this in our TEST environment successfully. Does anyone know how this could have happened - that GP calculated and assigned a depre...

Calculate error when one-to-many replationship is added
Table 1 ItemId (primary key) ItemName ItemDescription Table 2 Bil ItemID Quantity Received Table 3 No ItemID Quantity In I Create a query for each of the tables. which sums Quantity Received, Quantity In, Quantity Out I create another query which will add the Items table (table 1) and the other 3 queries summing up the items, joining the 3 queries to the Items table by ItemID using Join 2 (show all the fields in TblItem) for each join. Add eg ItemID, Item Name from the Items table then, from the other 3 queries, SumOfQuantity Received, SumOfQuantity In, SumOfQuantity Out the Query. In anoth...