Formula inn excel

T

tom.einar.eliassen

Hi, anyone know how too make a formula inn excel that can find how
many days there are left too "for exsample when people turn 50 years
from their birth?
 
S

Sandy Mann

Try:

="Will be "&DATEDIF(A1,TODAY(),"y")+1& "in "&
IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))-TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY())
&" Days time"

or with a bit more text:

=IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))=TODAY(),
"Birthday today!","Will be "&DATEDIF(A1,TODAY(),"y")+1&
" years old in
"&IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))>TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))-TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1))-TODAY())
&" Days time")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
M

MartinW

Hi Tom,

Nowhere near as fancy as Sandy's formula but try this.

Put the Birthdate in A1
Put the required age in B1
Put this in C1 =DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))-TODAY()
Make sure C1 is formatted as General

HTH
Martin
 
R

Ron Rosenfeld

Hi, anyone know how too make a formula inn excel that can find how
many days there are left too "for exsample when people turn 50 years
from their birth?
Click to expand...


=MIN(DATE(YEAR(DOB)+Yrs,MONTH(DOB)+{1,0},DAY(DOB)*{0,1}))-TODAY()

where

DOB is the Date of Birth
Yrs is the age in years.

Format the result as General, or you will get an unusual date as a result.
--ron
 
Top