Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2004-08-27
Medium Priority
1,277 Views
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
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

LVL 19

Expert Comment

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

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

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

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

Shauli earned 2000 total points
ID: 11914378
Or in your code:

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

S
0

LVL 18

Expert Comment

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

ID: 11914487
Dim dblDate as Double

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

LVL 19

Expert Comment

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

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

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

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

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

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

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

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

LVL 19

Expert Comment

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

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

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate rowâ€¦
Enums (shorthand for â€˜enumerationsâ€™) are not often used by programmers but they can be quite valuable when they are. Â What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that containsâ€¦
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â€¦
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â€¦
###### Suggested Courses
Course of the Month8 days, 16 hours left to enroll

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

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