Solved

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

Posted on 2004-08-27
17
1,265 Views
Last Modified: 2012-06-22
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?
0
Comment
Question by:efarhat
17 Comments
 
LVL 19

Expert Comment

by:Shauli
ID: 11914152
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
 
LVL 8

Expert Comment

by:mladenovicz
ID: 11914155
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 Comment

by:efarhat
ID: 11914328
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
 
LVL 19

Expert Comment

by:Shauli
ID: 11914358
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
 
LVL 19

Accepted Solution

by:
Shauli earned 500 total points
ID: 11914378
Or in your code:

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

S
0
 
LVL 18

Expert Comment

by:JR2003
ID: 11914452
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
 
LVL 18

Expert Comment

by:JR2003
ID: 11914487
Dim dblDate as Double

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

Expert Comment

by:Shauli
ID: 11914589
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:JR2003
ID: 11914959
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
 
LVL 19

Expert Comment

by:Shauli
ID: 11915217
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
 
LVL 18

Expert Comment

by:JR2003
ID: 11916154
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
 
LVL 19

Expert Comment

by:Shauli
ID: 11916245
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
 
LVL 18

Expert Comment

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




0
 
LVL 19

Expert Comment

by:Shauli
ID: 11916775
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
 
LVL 18

Expert Comment

by:JR2003
ID: 11917032
efarhat,
Give all the points to Shauli. He wants them more than me.     :)
JR
0
 
LVL 19

Expert Comment

by:Shauli
ID: 11917168
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
 
LVL 18

Expert Comment

by:JR2003
ID: 11917275
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now