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

Hello Experts:

I have several columns with dates and I want to go through each of them and subtract them from todays date and then divided it by 365.  Should be a double value (i.e. 2.1)

Any suggestions?
###### Who is Participating?

Commented:

bucketVal  = FormatNumber(DateDiff("d", d.value, Now) / 365, 1)

S
0

Commented:
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

0

Commented:
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.
0

Author Commented:
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
0

Commented:
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

0

Commented:
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.
0

Commented:
Dim dblDate as Double

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

Commented:
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
0

Commented:
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
0

Commented:
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
0

Commented:
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

Commented:
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
0

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

0

Commented:
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
0

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

Commented:
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
0

Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.