Formula Question (MPS)

i have a formula i use in a spreadsheet...
= IF(YEAR(O$1)-YEAR(E3)=105,,YEAR(O$1)-YEAR(E3))

I use it to caculate ages where the current date is in cell O1 and the
Person's birthday is shown in cell E3.

It doesn't add up right because if the birthday is later in the year
than the current day (shown in cell O1. )    It works fine if the
birthday is.  If the birthday has already happened this year, then the
formula works fine. 

How shoud i adjust it?  thanks.

Mike
0
crash613 (4)
6/2/2005 2:19:44 PM
excel 39879 articles. 2 followers. Follow

4 Replies
452 Views

Similar Articles

[PageSpeed] 21

instead of adjusting, how about using

    =DATEDIF(E3,O1,"y")

or

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

??

See

    http://cpearson.com/excel/datedif.htm

In article <u45u915gej9jerv0mm8p0qlvp5emvotp6v@4ax.com>,
 Michael P. Smith <crash613@forgetaboutit.com> wrote:

> i have a formula i use in a spreadsheet...
> = IF(YEAR(O$1)-YEAR(E3)=105,,YEAR(O$1)-YEAR(E3))
> 
> I use it to caculate ages where the current date is in cell O1 and the
> Person's birthday is shown in cell E3.
> 
> It doesn't add up right because if the birthday is later in the year
> than the current day (shown in cell O1. )    It works fine if the
> birthday is.  If the birthday has already happened this year, then the
> formula works fine. 
> 
> How shoud i adjust it?  thanks.
0
jemcgimpsey (6723)
6/2/2005 3:01:41 PM
Don't use it at all


http://www.cpearson.com/excel/datedif.htm

Regards,

Peo Sjoblom

"Michael P. Smith" wrote:

> i have a formula i use in a spreadsheet...
> = IF(YEAR(O$1)-YEAR(E3)=105,,YEAR(O$1)-YEAR(E3))
> 
> I use it to caculate ages where the current date is in cell O1 and the
> Person's birthday is shown in cell E3.
> 
> It doesn't add up right because if the birthday is later in the year
> than the current day (shown in cell O1. )    It works fine if the
> birthday is.  If the birthday has already happened this year, then the
> formula works fine. 
> 
> How shoud i adjust it?  thanks.
> 
> Mike
> 
0
PeoSjoblom (789)
6/2/2005 3:04:04 PM
Thanks for the two SPEEDY replies.. i wasn't going to check for a few
days, then just for the heck of it i checked now!   thanks!!

That works great, and corrects the problem... but i have one more
thing to ask, add?  Is there a way that if there is no date added, as
in you haven't filed it out yet, that it didn't return the number 105?

This is a spread sheet that i will be updating often... and when i
look at lines that are blank it shows the number 105 in the age box.
this isn't a huge issue, but i was wondering if there was a way around
this.

Mike


On Thu, 02 Jun 2005 09:01:41 -0600, JE McGimpsey
<jemcgimpsey@mvps.org> wrote:

>instead of adjusting, how about using
>
>    =DATEDIF(E3,O1,"y")
>
>or
>
>    =DATEDIF(E3,TODAY(),"y")
>
>??
>
>See
>
>    http://cpearson.com/excel/datedif.htm
>
>In article <u45u915gej9jerv0mm8p0qlvp5emvotp6v@4ax.com>,
> Michael P. Smith <crash613@forgetaboutit.com> wrote:
>
>> i have a formula i use in a spreadsheet...
>> = IF(YEAR(O$1)-YEAR(E3)=105,,YEAR(O$1)-YEAR(E3))
>> 
>> I use it to caculate ages where the current date is in cell O1 and the
>> Person's birthday is shown in cell E3.
>> 
>> It doesn't add up right because if the birthday is later in the year
>> than the current day (shown in cell O1. )    It works fine if the
>> birthday is.  If the birthday has already happened this year, then the
>> formula works fine. 
>> 
>> How shoud i adjust it?  thanks.

0
crash613 (4)
6/2/2005 4:01:35 PM
One way:

    =IF(E3="","",DATEDIF(E3,TODAY(),"y"))

In article <s6bu91hq0g8dqkhru994pap2vdq89rdvc5@4ax.com>,
 Michael P. Smith <crash613@forgetaboutit.com> wrote:

> Thanks for the two SPEEDY replies.. i wasn't going to check for a few
> days, then just for the heck of it i checked now!   thanks!!
> 
> That works great, and corrects the problem... but i have one more
> thing to ask, add?  Is there a way that if there is no date added, as
> in you haven't filed it out yet, that it didn't return the number 105?
> 
> This is a spread sheet that i will be updating often... and when i
> look at lines that are blank it shows the number 105 in the age box.
> this isn't a huge issue, but i was wondering if there was a way around
> this.
0
jemcgimpsey (6723)
6/2/2005 4:30:21 PM
Reply:

Similar Artilces:

Office XP Exel
Scenario: User's A,B, C, and D all have access to an excel XLS on a 2003 server. Users A & B have modify rights, users C&D only have read rights. If user A updates the file, the general tab in properties reflect the exact time the file was modified. After user A saves and closes the file, user D goes in. The changes are there, but in the properties general tab, the modify date in an old date (probably the actual creation date). Is this normal ? Any idea's ? ...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Thanks Ken Wright but one more question RE saving data from circular references
Thanks heaps for that macro Ken Wright . I have run it and it works. However it loops forever. Can I set it up so that it could save dat from a certain number of iterations? Thanks again Michae -- Message posted from http://www.ExcelForum.com Hi Michael, If you stayed within your thread and did not change the subject Ken would see your reply as a reply to his reply and would see it a lot faster than when you start a new thread. Ken would probably see your post highlighted in RED. Also someone other than Ken might have been able to help you. (not me I avoid anything that hints of circul...

Newbie question
'hello, can someone explain in plain English what this formula is actually saying: =IF(C32="FOB",(B28:C28/'Board Pricing and conversion'!B19)-B28,(B28/'Board Pricing and conversion'!B20)-B28) I understand the C32="FOB", but the rest I don't. I know it is referencing another spreadsheet but don't know what it is looking at?? Thanks bassman Double-click on the cell with the formula and you will see the syntax IF(logical_test, [value_if_true], [value_if_false]) You have a logical text for "FOB" on C32 of active sheet. If True the...

a simple math formula
Hi, I'm new to this and have a very simple question. I have values, simple numbers representing weight in kg, that I wish to automatically convert to US pounds. The 'kg' numbers are in fields B5 - to B77 for example. I want the conversion result (simply multiplying each B cell entry by 2.2) placed in the ''cell adjacent. Thanks! Dave Horne Hi David In C5 enter =CONVERT(B5,"kg","lbm") and copy down through C6:C77 -- Regards Roger Govier "Dave Horne" <davehorne@home.nl> wrote in message news:upOf6pgUJHA.4916@TK2MSFTNGP06.p...

Simple Question-How to create more than one transaction on the Acc
If there is a question already posted let me know. The question is: I created a bank account information on the Account list icon and want to have more than (one)transactions listed and see each payee displayed separately on each page so i could have all the months posted with due dates and total listed. Thank you. In microsoft.public.money, a.j. wrote: >If there is a question already posted let me know. The question is: I created >a bank account information on the Account list icon and want to have more >than (one)transactions listed and see each payee displayed separately ...

Array Formula #7
I have an array formula that works correct up to the 20th row. It quit running on row 20 or it does not pick up any information after row 20. Any help out ther -- pete576 ----------------------------------------------------------------------- pete5761's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2961 View this thread: http://www.excelforum.com/showthread.php?threadid=49427 Rather impossible to tell... What's the formula, and what cell(s) is it entered into? What type of data is in any referenced ranges? In article <pete5761.205n5a_1134773401.141...

Question about clip art, etc
Whenever I use clip art in Publisher or Word (2000) it says to insert the CD (#2) that has it on it. Which I do. Before I reinstalled WINDOWS a few weeks ago, and had to put everything in again, I had the clip art on my computer. So, if I needed something it was right there and I didn't need to keep putting in the CD. I have tried this several times, in Word (mainly, though I use clip art in Publisher too and have that function, using clip art, enabled- after it asked if I wanted to) The CD brings up the install and I click ADD OR REMOVE FEATURES and click on the clip art and "...

Labels in Formulas requires manual refresh?
I use Labels in Formulas extensively. (Excel 2003 11.5612.5606) That is, I tick off Options/Calculations/"Accept Labels in Formulas" and then use whatever text string I've placed at the top of a column of data to refer to it. It has many benefits over Named Ranges, dynamic or otherwise for my purposes, but one ongoing annoyance. When I add data to the bottom of the column of data, the in-cell formula using the label for reference does not change when it should. Workaround: Select cell, F2 or double click to open it, Enter. I have a number of such formulae so updating can be ...

Scheduling formula question
I know both are the same equation. Which one is by definition ? "Duration = Work / Units" or "Work = Duration x Units". I have a three day task with a resource assigned (Max. Units 100%, Units:100%). All calendars are the default Standard base calendar; Hours per day is 9 hours. How do we build the equation to calculate 27 hours of work ? TBol -- To be technically correct, the Duration Equation formula is written as: Duration = Work/(Hours Per Day x Units) You find the Hours Per Day value on the Calendar page of the Options dialog, accessed by clic...

Winfax question before purchase
i dont know where else to place this and there have been alot of posts in here relating to winfax, so here goes: I run a business and all of the time we get customers telling us they faxed something, and we have no record of it. now sometimes they never sent it...sometimes they sent it upside down and we received it blank..sometimes we just lost it. I am thinking that Winfax might be a solution to my problems, but I need to know a few things. A. can I set up winfax so that I plug my fax line into my comp, it receives the fax, and automatically prints it out to my printer (functioning jus...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Subform question 04-09-10
I have a form (Form1) that contains a subform (Subform1). Within this subform I have a combo box which, depending on what is chosen, pops up another form (Popup1)for additional information. I need this additional information in the form that pops up to be 'linked' with the subform. The problem I am running into is that when the user enters information in Popup1, the table has not been populated witht he data that is in the subform so there is no record to 'link' to. What is the best way to force te esubform to pass its information to the table? Thanks i...

outlook in sub-domain to set use root-domain question!!!
Dear Sir Please see below more details,(We are using special railway line between Head office in Taipei and branch office in Tao-Yuan) Head office in Taipei: aaa.com(Root domain) Dc server * 2(One of it is GC Server), Front-End Exchange 2003 *1, Back-End Exchange 2003 * 2(One is named mail1, another is named mail2 ) Branch office in Tao-Yuan: bbb.aaa.com(sub-domain) Dc Server *1(No GC Server,No Exchange Server) After using ADMT v3 Tool, when I transfer an account from root named aaa.com(ou) to bbb.aaa.com. After I ins...

Delete contents deletes all data and formulas
When I hit delete contents all data and formulas are deleted. How can I delete data without deleting formulas? Hi, You could try this tap F5 - Special - Constants - OK and if that selects the data you want to delete then tap the delete key -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "tdhcrr" wrote: > When I hit delete contents all data and formulas are deleted. How can I > delete data without deleting formulas? First use Find...

Excel Formula Help
I am setting up a basic excel spreadsheet and really have got no experience with excel. I want to do something real basic like if A2=laptop display $10 or if A2=desktop display $20 how do I do this? I think that if the list of options is more than a just a few, a =vlookup() function would work nicely. It may seem a little complex to start, but once you use it, you'll find tons of more reasons to use it. Debra Dalgleish has some nice instructions at: http://contextures.com/xlFunctions02.html BadSector wrote: > > I am setting up a basic excel spreadsheet and really have got no...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

creating nested formulas from drop down box
Hi, I have cell A1 with a drop down box containing 26 available choices. B1 has the dollar amount matching to the choice in A1 using vlookup. E1 totals several cells including B1 together. I want F1 to look at A1 and either enter the number from E1 or NA. Here's billing example: A1=January , B1=$5 ,C1=$10, D1=$1, E1=$16 (total of b-d1) F1 is the column for January G1 is the column for February H1 is the column for March, etc If A1 = Jan, then F1 should be $16 If A1 = Feb, then F1 should be NA or $0 This is the formula that has been working so far: =IF((G2="...

Input mask & Format question
Greetings I use the input mask \(999") "999\-9999 in the Phone field of my table. I wanted the brackets, space and dash stored in the table because the data is imported into other applications. 1234567890 is stored as (123) 456-7890. On one of my forms there is a combobox with fields CustID, CustName, Phone. My problem is how to format the phone number in the cbo. It shows up as 1234567890. Thanks in advance Becky Hi - Set the input mask to \(999") "999\-9999:0 (adding semicolon - zero). This forces the literals to be stored along with the other characters...

passeord Protect for excel formula and VBA code
Hi, i have excel sheet its contain lot of formula and VBA(macro) code, its for used for user purpose, i need how to protect the formula and VBA at the time of user using. kindly let me know , kindly help me out i need user password : for user can upload the data in non restriction cell, admin password : can change any thing(change power) pls help me, Please ask on the Excel board. "deen" wrote: > Hi, > > i have excel sheet its contain lot of formula and VBA(macro) code, > > its for used for user purpose, > > i need how to protect the formula and ...

Tiebreaker in a Index formula?
I need a tiebreaker for a formula. Right now the formula reads: =INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0)) which works real well But when there is a tie it shows on one store twice instead of listing the two separate stores. I have two stores have the exact scores and it list the store with the lower store number twice. For example I have store 598 and store 698 both with a score of 100% but in the ranking of the stores it shows store 598 twice instead of 598 then 698. Is it possible to have a tiebreaker, with the index formula, that can list the stores in descending order, 598 first...

Sales for Outlook Web Site question
Does CRM 1.2 Sales for Outlook client utilize Cassini? If not, is it supposed to copy files to \inetpub\wwwroot\ or repoint the Default Web Site to another directory? I am getting errors clicking on the Activities, Accounts, etc. and the "Promote E-mail to CRM Activity" button... Yes, it uses Cassini on port 2525. No, files don't need to be copied to inetpub. Mike "Jim Scavuzzo" <NOSPAM-scavuzzoj@ecg-inc.com> wrote in message news:eTbk8jW9DHA.2044@TK2MSFTNGP10.phx.gbl... > Does CRM 1.2 Sales for Outlook client utilize Cassini? If not, is it > suppose...

Graph question #2
Hi Folks, Quick query with regards to how data is read by a graph. I have a sheet featuring the number of staff assigned to the call centre at 1/2 hour points throughout the day. This data is stored in a sheet called 'Data' and in cells D17, E17, F17 etc etc. The data within this page is not very well presented as it is run automatically from the system we use here so I have made a more presentable table and graph. For the table for each 1/2 i simply reference the cell in the data page so that all i have to do to update the sheet week by week is paste fresh data in. Using =Data!D17 t...

locking / unlocking cell in formula
E F 1 Do you own a guitar validation cell (yes/no) 2 If yes is it a Gibson 3 Is it a 5 string 4 Is it electric 5 Do you own a piano 6 Is it electric 7 Does it have 88 keys 8 Do you own a TV 9 Is it color 10 Is it an LCD 11 Is it 25" 12 Is it 32" 13 Is it 42" 14 Do you own a radio I would like to lock cells F2:F4 if F1 contains the word "no" I would also like to lock cells F6:F7 if F5 contains the word "no" I would also like to lock ce...