Issue in Converting string to Date in VBA

Question

I need to write a function which will convert 2 string parameters to dates and return true/false after comparing the dates.

I have tried to implement this requirment by writing below mentioned code

Function CompareDate(StringDate1 As String, StringDate2 As String) As Boolean
    Dim Date1 As Date
    Dim Date2 As Date
    Date1 = CDate(StringDate1)
    Date2 = CDate(StringDate2)

    If Date1 > Date2 Then
      CompareDate = True
    Else
     CompareDate = False
    End If
End Function


Dim test As Boolean
test = CompareDate("03-Mar-2016 02:43 PST", "01-Mar-2016 11:33 PST")

But I am getting "Type Mismatch" error at this line Date1 = CDate(StringDate1).

Any idea what needs to be modified to fix the issue.

Note : My function also needs to consider time and time zone while comparing dates.

There is no simple function I am aware of that will allow you to pass a time zone. — Rory
– Rory, Commented May 5, 2016 at 15:56

Nathan_Sav · Accepted Answer · 2016-05-05 14:00:22Z

2

try cdate(split(StringDate1," ")(0)) and (1) will give you the time to check also, when date1=date2? or you can replace the PST part

edited May 5, 2016 at 14:00

answered May 5, 2016 at 13:54

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

refactor Over a year ago

Now the error got fixed , but it is not taking time part into consideration while comparing the dates . My function needs to consider time and time zone while comparing dates.

Mark Balhoff · Accepted Answer · 2016-05-05 17:00:48Z

The parse problem is with the presence of a timezone. There are a lot of ways to manage this date time depending on what your endgame is but the most straightforward is just to drop off the timezone and let CDATE parse it. In the following code I assume that a) all of your times are in PST and b) there is a timezone on the end of every date. If these assumptions are wrong, modifications are necessary. If essential, managing multiple time zones could be done by converting everything to one time zone and, if desired, tracking what time zone it came from. But I don't know of any standard VBA module that knows the time zones so that would need an array from you.

Function CompareDate(strDate1 As String, strDate2 As String) As Boolean
    Dim Date1 As Date
    Dim Date2 As Date
    Date1 = CDate(Left(strDate1, InStrRev(strDate1, " ")))
    Date2 = CDate(Left(strDate2, InStrRev(strDate2, " ")))
    CompareDate = (Date1 > Date2)
End Function

Maybe you could construct the timezone vs UTC offset array from the table here but I don't have time to process it right now. Pretty straightforward once you construct that array.

FatBoySlim7 · Accepted Answer · 2016-05-05 14:05:36Z

i think it would help if you formatted the date before doing CDate.

dateX = format(StringDate1, "mm/dd/yyyy")

You can also try somethign like this

dateX = cdate(format(stringdate1,"mm/dd/yyyy") - see that how works.

I don't have anythignt to test it with. If worst comes to worst, instead of passing the StringDate1 as string, pass it as Date and should be all set then. No need to do CDate in that case.

Function CompareDate(StringDate1 As Date, StringDate2 As Date) As Boolean

and Wherever you call this function CompareDate....do CDate then.. as such...

Call CompareDates(cdate(stringdate1), cdate(stringdate2))

basodre · Accepted Answer · 2016-05-05 14:18:19Z

0

The date portion gives you an integer value referring to the date, and the time portion gives you a fractional value for the time. So just add the two such as (using @Nathan_Sav code):

cdate(split(StringDate1," ")(0)) + cdate(split(StringDate1," ")(1))

answered May 5, 2016 at 14:18

basodre

5,7901 gold badge18 silver badges25 bronze badges

Comments

Tom · Accepted Answer · 2016-05-05 14:18:56Z

0

Give this a try:

Function CompareDate(StringDate1 As String, StringDate2 As String) As Boolean
    Dim Date1, Date2

    Date1 = Split(StringDate1, " ")
    Date2 = Split(StringDate2, " ")

    CompareDate = IIf(DateDiff("d", Date1(0), Date2(0) > 0 And DateDiff("n", Date1(1), Date2(1)) > 0), True, False)
End Function

answered May 5, 2016 at 14:18

Tom

9,9084 gold badges33 silver badges51 bronze badges

2 Comments

refactor Over a year ago

But your solution does not consider Time zone, am I right ?

Tom Over a year ago

You're correct. You could do this by looking at the third part of the split, however as far as I'm aware - Excel has no way natively of comparing timezones. I'd suggest converting all values to a UTC timestamp and then comparing those.

Collectives™ on Stack Overflow

Issue in Converting string to Date in VBA

5 Answers 5

1 Comment

Comments

Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

2 Comments

Related