Date Subtraction Equation 2

[ebisabes]

What formats & formulas do I use if I put one date in one cell & I want to know how long ago that date was from today. example.. if today is 9/14/2006 and I enter 7/7/2001 into a cell: what do I enter into another cell to return: 5.17 (for years)

 

[ThomasLafferty]

Hi!
=(Today()-A1)/365
Or whatever cell you have entered 7/7/2001 in. Some years have leap years, so 365 is approximate.

Tom

Born once die twice; born twice die once.

 

[fredericofonseca]

RTFM is always usefull.

Here is one example.

A
1-Date
2-6/9/2007
3-9/2/2007
4-12/10/2008
Formula Description (Result)
=MONTH(A3)-MONTH(A2) Months occurring between two dates in the same year (3)
=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3) Months occurring between two dates over a year apart (15)


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[DirkStruan]

There is also a Datedif function which I seem to remember reading is not longer documented in Help or listed in the functions but is a remnant of the Lotus 123 days.

Try:

Datedif(date1,date2,"d")

datedif(date1,date2,"M")

or

datedif(date1,date2,"Y")

date1<date2

For what you want you'd need "=Datedif(date1,date2,"M")/12

HTH

Dirk

 

[xlbo]

A caveat when working with dates - years, as has been stated are not consistent values - same goes for months.

For any time distribution data, you should really work in weeks or days as they are consistent lengths of time

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Llwynog]

I wonder if you could help me with a date function problem that is similar.
I want to calculate how long an employee has to work until he/she is 65 years of age i.e. Date of Birth (formatted YYYY) + 65 years - I know this sounds simplistic - but so am I !! (ha ha) - but I'm stuck on what looks to be a simple calc.
Any suggestions would be gratefully received.
Thanks

 

[Fenrirshowl]

=EDate(dateofbirth,12*65)

I think you need the analysis toolpack add-in (Tools menu\Add ins) selected to have the option of Edate.

Fen

 

[SkipVought]

or...
[tt]
DaysTil65: =date(year(dob)+65,month(dob),day(dob))-today()
[/tt]


Skip,
[sub]
[red][/red]
[/sub]