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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.