excel and leap years

[tony]

Hi everyone, i am writing this on behalf of someone else
who uses excel much, much more than i do. We are trying
to figure out how long an employee has been working for
the company, in this we need to account for sick
periods, layoff time, leave of absences, and leap years,
i know there is a formula out there to determine whether
or not a certain year is a leap year (lots of them) BUT
how would i take two dates say 10/03/76 - (march 10th
1976 - Canadian) and 11/13/04 and find out how many days,
months that would be INCLUDING leap years, I know that
00, 96, 92, 88, 84, 80,86 were all leap years but can't
excell figure this out and account for the actual days.

please excuse my rough description as i am not an
familiar with excel.

thanks

 

[Norman Harker]

Hi Tony!

Just use:

=B1-A1
Where B1 is =TODAY() or some other date and A1 is the starting date.
Format General.

Excel will automatically count the Leap Year days (unless employee
started before 1-Mar-1900 <vbg>)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Lady Layla]

Cant be done the way you are asking it as there is no month that is 13. What
way do you want to have your dates read?




: Hi everyone, i am writing this on behalf of someone else
: who uses excel much, much more than i do. We are trying
: to figure out how long an employee has been working for
: the company, in this we need to account for sick
: periods, layoff time, leave of absences, and leap years,
: i know there is a formula out there to determine whether
: or not a certain year is a leap year (lots of them) BUT
: how would i take two dates say 10/03/76 - (march 10th
: 1976 - Canadian) and 11/13/04 and find out how many days,
: months that would be INCLUDING leap years, I know that
: 00, 96, 92, 88, 84, 80,86 were all leap years but can't
: excell figure this out and account for the actual days.
:
: please excuse my rough description as i am not an
: familiar with excel.
:
: thanks

 

[Niek Otten]

Don't bother about the leap years; Excel has a built-in calendar which takes
that into account (except if you include the year 1900).
Look at this description of the Datedif function:

http://www.cpearson.com/excel/datedif.htm

and a lot of other stuff at this site about dates and times

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Norman Harker]

Hi Tony!

I now note that you want days and months. In most cases the following will
produce no problems:

=DATEDIF(A1,B1,"m")&" Months "&DATEDIF(A1,B1,"md")&" days"

You'll get interpretation problems though if the starting date is 31-Jan and
you look at the elapsed period on 1-Mar

Better to use a constant measure such as days. In addition you will be able
to use the output in subsequent calculations.

An alternative for apportionment / calculation of benefits will be to use
YEARFRAC but watch out because there are some bugs in that Analysis ToolPak
function.
--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.