Trying to figure out VBA Solution for text date minus 1

[Stephen_IV]

I have dates that are formatted a:
1012019
1022019
4012018

What I need to do is to take those dates and minus 1 from them:
1012019 = 12312018
1022019 = 1012019
4012018 = 3312018

I have a formula that I figured out below (It works but do not know if it the best solution)

=--TEXT(TEXT(A1,"00\/00\/0000")-1,"mmddyyyy")

What I am trying to do is to emulate this in VBA but I am stuck. I know in some of my other posts people say it is overkill to do it in VBA
but because I cannot do it in VBA, I need to learn and grow. Any help would be appreciated!

 

[mikerickson]

If your input is 1232019 what do you want the output to be 1222019 or 1222019?

To put it another way, does 1232019 represent 23. Jan 2019 or 3. Dec 2019 ?

 

[Stephen_IV]

Hi mikerickson the leading 0's are dropped.
The 12312018 represents December 31
The 1022019 represents 01022019

 

[mikerickson]

What number represents 23 Jan 2019?
What number represents 3 Dec 2019?

 

[mikerickson]

If the day portion retains its leading 0, try this

=TEXT(DATE(RIGHT(A1,4),LEFT(RIGHT("0"&A1,8),2),MID(RIGHT("0"&A1,8),3,2))-1, "mddyyyy")

 

[Stephen_IV]

What number represents 23 Jan 2019? 1232019
What number represents 3 Dec 2019? 12032019

 

[Stephen_IV]

mikerickson thank you very much, that works a for a formula but what would the VBA approach be?

 

[mikerickson]

Function NextDate(thisDate as String) As String
    Dim currentDate as Date
    thisDate = Right("0" & thisDate, 8)
    currentDate = DateValue(Right(thisDate,4), Left(thisDate,2), Mid(thisDate, 3, 2))
    NextDate = Format(currentDate - 1, "mddyyyy")
End Function

 

[Stephen_IV]

Hi mikerickson,

I am getting a Compile Error: Wrong number of arguments or invalid property assignment on this line
currentDate = DateValue(Right(thisDate,4), Left(thisDate,2), Mid(thisDate, 3, 2))

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1012019​

[/td][td="bgcolor:#CCFFCC"]12312018[/td][td="bgcolor:#CCFFCC"]B2: =DayPrior(A2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1022019​

[/td][td="bgcolor:#CCFFCC"]01012019[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

4012018​

[/td][td="bgcolor:#CCFFCC"]03312018[/td][td][/td][/tr]
[/table]

Function DayPrior(thisDate As String) As String
    DayPrior = Format(DateValue(Format(thisDate, "00\/00\/0000")) - 1, "mmddyyyy")
End Function