Calculating dates

K

Kwakkel

A quick question before i'm off to bed :)
I got several dates, all in the future. I'll just give an example, cause
it'll be hell to explain otherwise :p
I have a date (Belgian format) 26-07-2008 (= July 26th, 2008). Now I need to
know how many days have past from 26-07-2004 untill now. So, day and month
stay the same and year needs to change to the year before the current one.
Then count the days untill the date now.
I need this to calculate accrued intrest, should someone wonder (or if that
makes it easier to understand ;) )
Preferably, a short function (if possible) since it needs to be integrated
into a larger function within Excel 2003.
Thanks in advance and good night :)
 
B

Bob Phillips

Assuming your date is in A1

=A1-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))

if now is 26th July 2008

or

=TODAY()-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))

or if now is today

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
K

Kwakkel

That one works, but I'm afraid I made a mistake. My apologies.
This formula is correct for all dates with DAY-MONTH after today. It isn't
however for DAY-MONTH before today.
So, if my date is 24-06-2010, the formula returns 267, which is correct.
If my date however is 23-02-2009, it returns 389. While it should return 23.
Is there another easy way to do that? ... Or does this complicate things
much?
In any case, thanks for your help!
 
B

Bob Phillips

Would you care to explain why it should be 23, as for the life of me I don't
see it by the rules you have posted so far. That date gives a result of

TODAY()-23/02/2004, or 389, which is exactly as requested. 23 only happens
if the date uses this year, but you specifically state '... and year needs
to change to the year before the current one ...'.

Perhaps, and this is guessing, you mean

=TODAY()-(IF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))<TODAY(),DATE(YEAR(TODAY()
),MONTH(A1),DAY(A1)),DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1))))


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
K

Kwakkel

I know, i made a mistake in my explanation.
The idea is to calculate the intrest from a bond. The intrest is paid every
year. So, it's impossible to have unpaid intrest for more than 1 year :)
It's a bit like a YEAR-TO-DATE, where the YEAR has an extra condition I
guess.
As for your new function: it's exactly what I need :) Thanks a lot!
 
Top