Using DateDiff () like Excel formula =("15-Oct-2007" - TODAY()) / 365

To know the different between today and any givven date, all you need to do is:

days =  DateDiff("d", anygivendate, Now)

For example:

MsgBox DateDiff("d", "1/1/2004", Now)

S

There is DAY360 that returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations.

this is what i came up with, would this work?  

      For Each d In .Cells
                bucketVal = (d.value - Now) / 365
                d.value = bucketVal
            Next d

Wait a minute, you want the result by years and not days, then do this:

yourvalue = FormatNumber(DateDiff("d", "1/1/2004", Now) / 365, 1)

This will have the years with 1 decimal digit. If you want more decimals, simply replace the 1 at the end with the required decimal number.

S

Dim dblDate as Double

dblDate = Now() - CDate(MyDate)


Msgbox dblDate

dblDate will show something like 3.4567 which would mean just under 3.5 days.

Dim dblDate as Double

dblDate = (cDate("15-Oct-2007") - now())/365

You dont need the cdate if you use datediff, as datediff can take any date format, as in:

MsgBox FormatNumber(DateDiff("d", "15-Oct-2000", Now) / 365, 1)

S

Shauli,

No, but you have to use DateDiff. So what's the benefit?

        dblDate = (CDate("15-Oct-2007") - Now()) / 365
runs over twice as quickly as
       dblDate = DateDiff("d", "15-Oct-2000", Now) / 365
and 3 times as quickly as
        strDate = FormatNumber(DateDiff("d", "15-Oct-2007", Now) / 365, 1)

JR

I would agree if your solution had formated the result to 1 or 2 decimals (as requested). As it does not, then you still have to format it, so faster or not is not the issue, dude :)

S

Ok, but the spec actually says " double value (i.e. 2.1)" so the actual answer according to the spec is:

    Dim dblDate As Double
    dblDate = CDbl(Format((CDate("15-Oct-2007") - Now()) / 365, "00.0")

Yours returns a String not a Double!   na na na na naaa  ;)

0)))))

but since when formatnumber returns a string? (Returns an expression formatted as a number)

So
As bucketVal  is a double (from asker code) then

bucketVal  =  FormatNumber(DateDiff("d", Now, "15-Oct-2007") / 365, 1)

would not change bucketVal  to a string, or maybe it will? ;)

S

As far as I'm aware FormatNumber has always returned as string. Or do you know something different? lol

ok. enough, we are confusing the asker, and it does not contribute to the question. We are saying the same from two legitimate approches. Have a great weekend.  :)

Shauli

efarhat,
Give all the points to Shauli. He wants them more than me.     :)
JR

JR2003,

I find it hard to understand your last comment. Even as a joke, it is a bad one. As far as I am concerened, this question is over. I unsubscribe to this question.

Again, have a nice weekend.

S

Shauli,
No offence intended. It was just a joke - I accept maybe not a very funny one from your perspective - but it was just a joke. Have a nice weekend too.
JR