Solved

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

Posted on 2004-08-27
17
1,268 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

803 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