Solved

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

Posted on 2004-08-27
17
1,262 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Or in your code:

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

S
0
 
LVL 18

Expert Comment

by:JR2003
Comment Utility
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
Comment Utility
Dim dblDate as Double

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

Expert Comment

by:Shauli
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 18

Expert Comment

by:JR2003
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
efarhat,
Give all the points to Shauli. He wants them more than me.     :)
JR
0
 
LVL 19

Expert Comment

by:Shauli
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

763 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

6 Experts available now in Live!

Get 1:1 Help Now