Solved: Function to return Date based on Current Date + number of Days?

[Saladsamurai]

I am looking to write a function that returns a Future Date in mm/dd/yy based on the current date and a specified number. For example, if the current date is 12/08/09

FutureDate(5) = 12/13/09

Any ideas?

[Bob Phillips]

Why not just

=The_Date+5

[Saladsamurai]

Why not just

=The_Date+5

I had no idea that Excel would handle this so well:

Sub Test()
Dim The_Date

The_Date = Date + 100

MsgBox The_Date
End Sub

I am surprised!

[Saladsamurai]

What if I wanted to know how many days have elapsed between ANY two dates?

[Bob Phillips]

Again

=Later_Date-Earlier_Date

[Saladsamurai]

Sorry. I don't get it. What kind of format does Excel want for the dates?

This obviously does not work

Sub Test()
Dim New_Date
Dim Old_Date
Dim Difference

New_Date = "December 12, 2009"
Old_Date = "December 6, 2009"

Difference = New_Date - Old_Date

MsgBox Difference



End Sub



Nor does this (for the obvious reason)
Sub Test()
Dim New_Date
Dim Old_Date
Dim Difference

New_Date = 12/12/2009
Old_Date = 12/06/2009

Difference = New_Date - Old_Date

MsgBox Difference



End Sub



or this

Sub Test()
Dim New_Date
Dim Old_Date
Dim Difference

New_Date = "12 / 9 / 2009"
Old_Date = "12 / 6 / 2009"

Difference = New_Date - Old_Date

MsgBox Difference



End Sub


So maybe you could elaborate a little on this


Again

=Later_Date-Earlier_Date?

Thanks.

[JKwan]

Try this:

Sub Test()
Dim New_Date As Date
Dim Old_Date As Date
Dim Difference As Long

New_Date = "12/12/2009"
Old_Date = "12/6/2009"

Difference = New_Date - Old_Date

MsgBox Difference
End Sub

[Bob Phillips]

Sub Test()
Dim New_Date
Dim Old_Date
Dim Difference

New_Date = #12/12/2009#
Old_Date = #12/6/2009#

Difference = New_Date - Old_Date

MsgBox Difference
End Sub

[Bob Phillips]

or



Sub Test()
Dim New_Date As Date
Dim Old_Date As Date
Dim Difference

New_Date = DateSerial(2009, 12, 12)
Old_Date = DateSerial(2009, 12, 6)

Difference = New_Date - Old_Date

MsgBox Difference
End Sub

[Bob Phillips]

Try this:

Sub Test()
Dim New_Date As Date
Dim Old_Date As Date
Dim Difference As Long

New_Date = "12/12/2009"
Old_Date = "12/6/2009"

Difference = New_Date - Old_Date

MsgBox Difference
End Sub


That returns 183 here when I am sure the OP wants 6.

[geekgirlau]

You need to be careful with dates, as all Microsoft products handle them in a very US-centric manner, despite any formatting you may have applied. When I see "12/6/2009" I read that as 12-June, whereas depending on your geographical location you may read it as 6-Dec.

The moral of our story is don't assume: xld's method using DateSerial removes any ambiguity.