CODEWITHSUNDEEP
excelvbadatesubtractiondateadd
i.fathy
i.fathy

Reputation: 33

VBA Cannot Subtract Days From Date

I’m trying to get the date 4 days older than now. Here is my code

Sub calcdate()
Dim nDateTime, oDateTime As Date
nDateTime = Format(Now, "YYMMDD")
oDateTime = Format(DateAdd("d", -4, nDateTime), "YYMMDD")
MsgBox ("Today is " & nDateTime & " And OldDate is " & oDateTime)
End Sub

But output is ‘Today is 170604 And OldDate is 02-10-3734”

I Tried to do it without DateAdd

nDateTime = Format(Now, "YYMMDD")
oDateTime = nDateTime - 4

*Output is ‘Today is 170604 And OldDate is 170604’

I tried these changes

nDateTime = Format(Now, "YYMMDD")
oDateTime = DateAdd("d", -4, nDateTime)

*Output is ‘Today is 170604 And OldDate is 31-01-2367’

nDateTime = Format(Now, "YY-MM-DD")
oDateTime = Format(DateAdd("d", -4, nDateTime), "YY-MM-DD")

*Output is ‘Today is 17-06-04 And OldDate is 04-06-2013’

So it seems here it is subtracting years although I put “d”.

last thing i tried:

nDateTime = Format(Now, "DD-MM-YY")
oDateTime = Format(DateAdd("d", -4, nDateTime), "DD-MM-YY")

*Output is ‘Today is 04-06-17 And OldDate is 31-05-2017’ i want this date, in format 170531, also why did it give me 2017 while i choose format YY only?

Upvotes: 1

Views: 4204

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

because Format(Now, "YYMMDD") result is string

Dim myDate As Date
Dim nDatetime As String, oDateTime As String

myDate = Now
nDatetime = Format(myDate, "yymmdd")
oDateTime = Format(DateAdd("d", -4, myDate), "YY-MM-DD")

Upvotes: 0

Absinthe
Absinthe

Reputation: 3391

Format creates a string so you can no longer sensibly perform calculations on it. Do your formatting afterwards instead:

Dim nDateTime As Date, oDateTime As Date
nDateTime = Now
oDateTime = nDateTime - 4
MsgBox "Today is " & Format(nDateTime, "YYMMDD") & " And OldDate is " & Format(oDateTime, "YYMMDD")

Also, you need to declare the variable type for each variable so, as I've done.

Upvotes: 1

Related Questions