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!

 

[Stephen_IV]

Thanks shg! That did it! Much appreciated! Thank you again!!!!

 

[shg]

You're welcome.

Mike's function is fine also with DateValue changed to DateSerial.

 

[Rick Rothstein]

Here is one more way if could be done...

[table="width: 500"]
[tr]
	[td]Function DayPrior(thisDate As Long) As String
  DayPrior = Evaluate("TEXT(TEXT(" & thisDate & ",""00\/00\/0000"")-1,""mmddyyyy"")")
End Function[/td]
[/tr]
[/table]

which, of course, means this Excel formula solution would also work...

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

Mike's function is fine also with DateValue changed to DateSerial.

And once that is done, Mike's function can be collapsed into a one-liner to match the other offerings here...

[table="width: 500"]
[tr]
	[td]Function NextDate(thisDate As String) As String
  NextDate = Format(DateSerial(Right(thisDate, 4), Left(Right("0" & thisDate, 8), 2), Left(Right(thisDate, 6), 2) - 1), "mmddyyyy")
End Function[/td]
[/tr]
[/table]

 

[Stephen_IV]

Thanks also Rick,
Not knowing much about evaluate, I have a question, I assume that Evaluate is used so that we do not have to use application.worksheetfunction for text i.e. (application.worksheetfunction.text)?

 

[Rick Rothstein]

Thanks also Rick,
Not knowing much about evaluate, I have a question, I assume that Evaluate is used so that we do not have to use application.worksheetfunction for text i.e. (application.worksheetfunction.text)?

Not exactly. While it could be used that way in some cases, the Evaluate function (with some need for coaxing in certain circumstances) gives the programmer the ability to calculate the result of certain types of Excel formulas. Note what I wrote as the argument to the Evaluate function and the formula I gave in that same message... do you see the similarity between them?

 

[Stephen_IV]

Thanks Rick! Yes I do. Just watched a few videos and got a better understanding. Thanks again for your help!