Getting Overflow Error in Excel when using Format() to format as date

Question

I have a vba automation to concatenate some text but it's been giving me the "Overflow" error when changing the result to date in this specific file (works fine in others).

Function FormatAsDate(value As Variant, isDateColumn As Boolean) As String
    ' Format the value as a date if it's a valid date or a date serial number
    If isDateColumn And IsNumeric(value) Then
       ** FormatAsDate = Format(CDbl(value), "mm/dd/yyyy")**
    Else
        FormatAsDate = CStr(value)
    End If
End Function

I've tried to use less rows (down to 10 rows) and nothing. Could it be hidden formatting in my file? Please help!

You should always be suspicious of hidden characters in untrusted data (think - user input, unknown input). For instance, I can't tell you how many times leading or trailing spaces cause errors, or unprintable characters copied in with copy/paste from web pages. — topsail
– topsail, Commented Aug 21, 2024 at 21:59
That value is too large to convert to a date. ? format(10052026,"mm/dd/yyyy") in the Immediate window also gives "overflow" — Tim Williams
– Tim Williams, Commented Aug 21, 2024 at 22:09
Without the rest of the code and some sample data there's not much else we could suggest. — Tim Williams
– Tim Williams, Commented Aug 21, 2024 at 22:20

AcsErno · Accepted Answer · 2024-08-23 08:29:23Z

0

Try something like this:

If value Like "##/##/####" then Return(CDate(value))
If CLng(value) > 40000 and CLng(value) < 50000 then Return(CDate(value)) ' 2009 - 2036
If value Like "########" Then Return(DateSerial(Right(value,4), Left(value,2), Mid(Value,3,2))
Debug.Print "Error..."

answered Aug 23, 2024 at 8:29

AcsErno

1,6451 gold badge9 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Getting Overflow Error in Excel when using Format() to format as date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related