How to calculate the date??

[lamyuwang]

Hello everybody,

I could like to ask how to calculate the date by using excel??
e.g. from 1 may 2002 to 15 march 2005, what is the duration?
i.e. XX years, YY months, ZZ days??

does anyone can help me??

thanks a lot

 

[littlewoman]

excel displays a date but in reality uses a whole number as identifier for that date. excel counts the number of days as from januray 1, 1900 to the date entered and that is the identifier which makes it possible to count days.
(you can see the number when you enter a date in a cell formatted as number.)

now say in cell a1 there is 1 may 2005 and in cell b1 there is 15 march 2005 the formula to calculate years, months and days would look like:
Years:
=YEAR(B1)-YEAR(A1)
Months:
=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)
Days:
=B1-A1

the cell(s) where you put these formula's need to be formated as number otherwise you get a "strange date" yet the cells a1 and b1 in this example are formated as date.

there is also more info available (as ie weeks etc.) in the help files of excel.

grtz.

 

[ShabanaHafiz]

=DATEDIF("1/5/2002","15/3/2005","y") & " years, " & DATEDIF("1/5/2002","15/3/2005","ym") & " months, " & DATEDIF("1/5/2002","15/3/2005","md") & " days

 

[littlewoman]

hihihihi more ways leading to Rome
all in one cell.

nice one ShabanaHafiz

 

[lamyuwang]

Thanks all the above, may be I more specify my question, e.g. 1-mar-2004 to 2-april-2005
is 1 day, 1 month and 1 year

e.g. 1-feb-2004 to 1-mar-2005
is 0 day, 1 month and 1 year

but due to different day in each month, and year(for feburary), so how to calculate it??

 

[xlbo]

question - how many years between X & Y

answer - please define "a year - especially if you want to try and tack months and days on"

A year is a non discreet length of time that is variously represented as 365, 365.25, 365.25...... days respectively. It is also 12 months - although the number of days in those 12 months may vary

In terms of representing time passing in months, you have an even bigger issue - if the time passed is 9 months, how long is that ?? 9 * 28 ?? 9 * 30 ?? 9 * 31 ?? some random combination of all 3 ??

If you need to do any firther calculations on this data, including averaging / segmenting etc I would strongly suggest you find a discreet time period (the longest of these defined is a week) and use that instead

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