Solved

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

Posted on 2004-08-27
17
1,274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

717 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