Amount of time between dates

[karlcsr]

Hello all,

My last post gave me a super quick answer to what I couldn't figure
out after days and days of personal searching and trial and error.
What a great user group! Got another question that I hope is fairly
straight forward, but it is a new area to me, dealing with dates.

Basically, the very first column contains dates. To be precise, they
are the dates of stock market transactions. For the past six months,
I have been estimating that there have been 252 trading days a year,
and then figuring out annual returns and such by dividing, let's say,
834 tradings days by 252, so I would get a rate of return for 3.3
years.

Unfortunately life is not that easy. In the past, there were less
holidays, and there were more trading days. Every year, the 1st of
January falls on a different day of the week, so there may be 251 or
253 trading days.

So that is probably more info than you need, but I am ready to get
more accurate and wanting to know if there is a formula whereby Excel
can take the first trading day, let's say Jan. 1, 2000, and the last
date, let's say March 30, 2001 and convert that to a number such as
1.25.

Thanks all....Karl
(e-mail address removed)

 

[Andy Brown]

Check out the NETWORKDAYS function.

Help quote ; "If this function is not available, run the Setup program to
install the Analysis ToolPak. After you install the Analysis ToolPak, you
must enable it by using the Add-Ins command on the Tools menu".

Rgds,
Andy

 

[Frank Kabel]

Hi
to get the difference between these two dates in days you may use
NETWORKDAYS:
=NETWORKDAYS(A1,B1,list_of_holidays)

 

[Bob Phillips]

Karl,

NETWORKDAYS will give the number of working days, so you would need to
create a holiday list of non-trading days.

But ... your example ends up with 1.25. Are you after the number of years
not days? If so, how to you square off differing days in years?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi Karlcsr!

To get the number of working days in the current year use:

NETWORKDAYS(YEAR(A1),MONTH(A1),0),DATE(YEAR(A1),12,31),Holidays)

Holidays is a range containing holiday dates to be excluded if not
already excluded because they fall on a weekend. But here, watch out
for substitute holidays for (eg) Christmas Day and Boxing Day 2004.

To get the working days over a longer time span change the dates you
are using for start_date and end_data and make sure you have all
holidays for that extended period.

However, your method of calculating annual return is pretty suspect.
To get the annual capital appreciation / depreciation you should be
comparing the stock price at the end of the year with the stock price
at the beginning. You can use WORKDAY with second argument of 1 from a
base date of 31-Dec to get the first trading day and WORKDAY with a
second argument of -1 from a base date of 1-Jan to get the last
trading day.

--
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.

 

[karlcsr]

Bob,

Yes, I am after the number of years, on a percentage basis. Looking
at prior posts, I was able to "discover" the DATEDIF function, which I
was able to utilize. First I figured the number of years between the
dates with the "Y" variety of the DATEDIF function, and then there is
another function under DATEDIF, I think it is "YD" or something, and
it just figured the number of days between the two dates. So I would
combine the number of years and put the return of the number of days
divided by 365 to get a number like 3 and 27/365th of a year. Might
be messy, but it works great!

THanks so much and best wishes...
Karl