ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How to calculate with years....? (https://www.excelbanter.com/excel-programming/344844-how-calculate-years.html)

Andrew
How to calculate with years....?
 
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

Roger Govier
How to calculate with years....?
 
Hi

One way
=YEAR(TODAY())-YEAR(A1)
or
=DATEDIF(a1,TODAY(),"y")


--
Regards

Roger Govier


"Andrew" <NoToSPAM@home wrote in message
...
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

Andrew
How to calculate with years....?
 
These Either return:
Sat-14/Apr/1900
Or
105

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

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


"Roger Govier" wrote in message
...
| Hi
|
| One way
| =YEAR(TODAY())-YEAR(A1)
| or
| =DATEDIF(a1,TODAY(),"y")
|
|
| --
| Regards
|
| Roger Govier
|
|
| "Andrew" <NoToSPAM@home wrote in message
| ...
| 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
|
|
|
|



Nigel
How to calculate with years....?
 
Most flexible way to control all elements of the date.....

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



--
Cheers
Nigel



"Andrew" <NoToSPAM@home wrote in message
...
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

Roger Govier
How to calculate with years....?
 
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


"Andrew" <NoToSPAM@home wrote in message
...
These Either return:
Sat-14/Apr/1900
Or
105

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

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


"Roger Govier" wrote in message
...
| Hi
|
| One way
| =YEAR(TODAY())-YEAR(A1)
| or
| =DATEDIF(a1,TODAY(),"y")
|
|
| --
| Regards
|
| Roger Govier
|
|
| "Andrew" <NoToSPAM@home wrote in message
| ...
| 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
|
|
|
|

Roger Govier
How to calculate with 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))

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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


"Andrew" <NoToSPAM@home wrote in message
...
These Either return:
Sat-14/Apr/1900
Or
105

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

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


"Roger Govier" wrote in message
...
| Hi
|
| One way
| =YEAR(TODAY())-YEAR(A1)
| or
| =DATEDIF(a1,TODAY(),"y")
|
|
| --
| Regards
|
| Roger Govier
|
|
| "Andrew" <NoToSPAM@home wrote in message
| ...
| 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
|
|
|
|

Roger Govier
How to calculate with years....?
 
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


"Roger Govier" wrote in message
...
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


"Roger Govier" wrote in message
...
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


"Andrew" <NoToSPAM@home wrote in message
...
These Either return:
Sat-14/Apr/1900
Or
105

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

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


"Roger Govier" wrote in message
...
| Hi
|
| One way
| =YEAR(TODAY())-YEAR(A1)
| or
| =DATEDIF(a1,TODAY(),"y")
|
|
| --
| Regards
|
| Roger Govier
|
|
| "Andrew" <NoToSPAM@home wrote in message
| ...
| 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
|
|
|
|

Andrew
How to calculate with years....?
 
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
-----------------------------------------
"Roger Govier" wrote in message
...
| 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
|
|
| "Roger Govier" wrote in message
| ...
| 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
|
|
| "Roger Govier" wrote in message
| ...
| 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
|
|
| "Andrew" <NoToSPAM@home wrote in message
| ...
| These Either return:
| Sat-14/Apr/1900
| Or
| 105
|
| Not.... 16/12/2005 ....?
|
| Sorry Roger your Not answering the question.
| Thanx for trying though.
|
|
| "Roger Govier" wrote in message
| ...
| | Hi
| |
| | One way
| | =YEAR(TODAY())-YEAR(A1)
| | or
| | =DATEDIF(a1,TODAY(),"y")
| |
| |
| | --
| | Regards
| |
| | Roger Govier
| |
| |
| | "Andrew" <NoToSPAM@home wrote in message
| | ...
| | 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
| |
| |
| |
| |
|
|
|
|
|
|
|
|



Roger Govier
How to calculate with years....?
 
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"

--
Regards

Roger Govier


"Andrew" <NoToSPAM@home wrote in message
...
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
-----------------------------------------
"Roger Govier" wrote in message
...
| 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
|
|
| "Roger Govier" wrote in message
| ...
| 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
|
|
| "Roger Govier" wrote in message
| ...
| 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
|
|
| "Andrew" <NoToSPAM@home wrote in message
| ...
| These Either return:
| Sat-14/Apr/1900
| Or
| 105
|
| Not.... 16/12/2005 ....?
|
| Sorry Roger your Not answering the question.
| Thanx for trying though.
|
|
| "Roger Govier" wrote in message
| ...
| | Hi
| |
| | One way
| | =YEAR(TODAY())-YEAR(A1)
| | or
| | =DATEDIF(a1,TODAY(),"y")
| |
| |
| | --
| | Regards
| |
| | Roger Govier
| |
| |
| | "Andrew" <NoToSPAM@home wrote in message
| | ...
| | 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
| |
| |
| |
| |
|
|
|
|
|
|
|
|


All times are GMT +1. The time now is 05:17 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com