How to calculate with years....?

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

How to calculate with years...

IE: Date in Cell(A1) 16/12/1993
Value in B(1)= 12 (Years)

should give date 16/12/2005
 
These Either return:
Sat-14/Apr/1900
Or
105

Not.... 16/12/2005 ....?

Sorry Roger your Not answering the question.
Thanx for trying though.


| Hi
|
| One way
| =YEAR(TODAY())-YEAR(A1)
| or
| =DATEDIF(a1,TODAY(),"y")
|
|
| --
| Regards
|
| Roger Govier
|
|
| | > How to calculate with years...
| >
| > IE: Date in Cell(A1) 16/12/1993
| > Value in B(1)= 12 (Years)
| >
| > should give date 16/12/2005
| > -------------------
| > TIA
| > Andrew
| >
| >
|
|
 
Most flexible way to control all elements of the date.....

=DATE(YEAR(A1)+12,MONTH(A1),DAY(A1))
 
Hi Andrew

Then you can't have 16/11/1993 in A1
If I have A1 blank then I get 105 in both cases. You are seeing 14/apr/1900
because your cell is formatted as date and 14/04/1900 is 105 days after
01/01/1900. Format the cell as general and you will see 105 in both cases.

Enter 16/11/1993 in A1 and you will see 12 and 11, the answer of 11 being
because we have not yet reached 16/11/2005 so we are still about 6 weeks
short of 12 years.
 
Hi Andrew

I do most humbly apologise. I must learn to read first and post second!!<bg>
Just as I hit the send button, I reread your posting and saw that you wanted
to add 12 years to 16/11/1993, not find the number of years between the two.

=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))
 
Andrew

It's not my day.
That should have read
=DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))
but I'm sure you realised that.
Now where's that coffee pot??
 
Thanx Roger.

That's exactly what I wanted.
I was doing it a hard way;
Converting date to string, taking Year converting to value and adding value
then re concatenating the string and converting to date.
Think I need coffee to... ;-)

DOB Age
Next Birthday on: Birthday Countdown:
Thu-16/Dec/1993 11:Yrs 10:Months 21:Days
Fri-16/Dec/2005 1:Months 10:Days
Sun-12/May/1996 9:Yrs 5:Months 25:Days
Fri-12/May/2006 6:Months 6:Days
Thu-01/Sep/1994 11:Yrs 2:Months 5:Days
Fri-01/Sep/2006 9:Months 26:Days
Wed-12/Nov/1997 7:Yrs 11:Months 25:Days
Sat-12/Nov/2005 0:Months 6:Days
Mon-28/Aug/1978 27:Yrs 2:Months 9:Days
Mon-28/Aug/2006 9:Months 22:Days
Mon-17/Jul/1967 38:Yrs 3:Months 20:Days
Mon-17/Jul/2006 8:Months 11:Days
Fri-12/May/1972 33:Yrs 5:Months 25:Days
Fri-12/May/2006 6:Months 6:Days
Wed-05/Jan/1944 61:Yrs 10:Months 1:Days
Thu-05/Jan/2006 1:Months 30:Days
Thu-19/Feb/1942 63:Yrs 8:Months 18:Days
Sun-19/Feb/2006 3:Months 13:Days
Fri-16/Jan/2004 1:Yrs 9:Months 21:Days
Mon-16/Jan/2006 2:Months 10:Days

Kids are getting to clever posing me the question- "How long untill my next
birthday dad..?"

Thanx again for your time Roger.
Andrew
-----------------------------------------
| Andrew
|
| It's not my day.
| That should have read
| =DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))
| but I'm sure you realised that.
| Now where's that coffee pot??
| ---
| Regards
|
| Roger Govier
|
|
| | > Hi Andrew
| >
| > I do most humbly apologise. I must learn to read first and post
| > second!!<bg>
| > Just as I hit the send button, I reread your posting and saw that you
| > wanted to add 12 years to 16/11/1993, not find the number of years
between
| > the two.
| >
| > =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))
| >
| > --
| > Regards
| >
| > Roger Govier
| >
| >
| > | >> Hi Andrew
| >>
| >> Then you can't have 16/11/1993 in A1
| >> If I have A1 blank then I get 105 in both cases. You are seeing
| >> 14/apr/1900 because your cell is formatted as date and 14/04/1900 is
105
| >> days after 01/01/1900. Format the cell as general and you will see 105
in
| >> both cases.
| >>
| >> Enter 16/11/1993 in A1 and you will see 12 and 11, the answer of 11
being
| >> because we have not yet reached 16/11/2005 so we are still about 6
weeks
| >> short of 12 years.
| >>
| >> --
| >> Regards
| >>
| >> Roger Govier
| >>
| >>
| >> | >>> These Either return:
| >>> Sat-14/Apr/1900
| >>> Or
| >>> 105
| >>>
| >>> Not.... 16/12/2005 ....?
| >>>
| >>> Sorry Roger your Not answering the question.
| >>> Thanx for trying though.
| >>>
| >>>
| >>> | >>> | Hi
| >>> |
| >>> | One way
| >>> | =YEAR(TODAY())-YEAR(A1)
| >>> | or
| >>> | =DATEDIF(a1,TODAY(),"y")
| >>> |
| >>> |
| >>> | --
| >>> | Regards
| >>> |
| >>> | Roger Govier
| >>> |
| >>> |
| >>> | | >>> | > How to calculate with years...
| >>> | >
| >>> | > IE: Date in Cell(A1) 16/12/1993
| >>> | > Value in B(1)= 12 (Years)
| >>> | >
| >>> | > should give date 16/12/2005
| >>> | > -------------------
| >>> | > TIA
| >>> | > Andrew
| >>> | >
| >>> | >
| >>> |
| >>> |
| >>>
| >>>
| >>
| >>
| >
| >
|
|
 
Hi Andrew

Then I think I would have been doing it as follows.
Birthday in A8
in A9
=IF(TODAY()>DATE(YEAR(TODAY()),MONTH(A8),DAY(A8)),DATE(YEAR(TODAY())+1,MONTH(A8),DAY(A8)),DATE(YEAR(TODAY()),MONTH(A8),DAY(A8)))
in B8
=DATEDIF(A8,TODAY(),"y")&":Years "&DATEDIF(A8,TODAY(),"ym")&":months
"&DATEDIF(A8,TODAY(),"md")&":days"
in B9
=DATEDIF(TODAY(),A9,"ym")&" : Months "&DATEDIF(TODAY(),A9,"md")&" : Days"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calc Number of Months PER YEAR Between Dates 2
Excel Microsoft excel interest formula 2
Mid-Month function help 3
Formula to calculate exact months and days between two dates 3
How to substract a date in excel to 2000 1
Based on a condition in one column, search for a year in another column, and display data from anoth 2
calculating Sick pay entitlement 1
Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the 7

Back
Top