PDA

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

Saladsamurai
12-08-2009, 11:05 AM
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
12-08-2009, 11:36 AM
Why not just

=The_Date+5

Saladsamurai
12-08-2009, 11:41 AM
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
12-08-2009, 11:45 AM
What if I wanted to know how many days have elapsed between ANY two dates?

Bob Phillips
12-08-2009, 11:50 AM
Again

=Later_Date-Earlier_Date

Saladsamurai
12-09-2009, 07:11 AM
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
12-09-2009, 07:29 AM
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
12-09-2009, 08:08 AM
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
12-09-2009, 08:10 AM
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
12-09-2009, 08:11 AM
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
12-09-2009, 05:14 PM
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.