Wierd Bug with Format Date VBA???

[drolfe]

sub stuff()
theDate = Format(Now,"mm-dd-yy")
themonth = Month(theDate)
theDay = Day(theDate)
wkday = Format(theDay, "ddd")
end sub

..... for some reason when i use the Format() function, it takes the day
before. Is there an option statement that I am missing or something?
The first format string returns the correct day, but the second one
returns the previous day. Am i missing an option statement?

 

[Martin Fishlock]

Hi,

The functions Month(), Day(), Format() (and Year()) expect a date but not
as a text string but in Excels internal date format.

To solve it you can use another variable to record date or use the now
function or extract the details from the string:

dim tempDate as Date ' new variable
tempDate = Now ' set it to date
theDate = Format(Now,"mm-dd-yy") ' as before
theDate = Format(tempDate,"mm-dd-yy") 'using temp variable
themonth = Month(Now) ' as before with Now
themonth = Month(tempDate) ' using temp variable
themonth = format(Now,"mmmm") ' with Now giving the full month
themonth = left(theDate,2) ' extracting from the string
theDay = Day(Now) ' as before with Now
theDay = Day(tempDate) 'using temp variable
theDay = format(Now, "d") ' as before with Now
theDay = mid(theDate, 4,2) ' extracting from the string
wkday = Format(Now, "ddd") ' as before with Now
wkday = Format(tempDate, "ddd") 'using temp variable

 

[Dave Peterson]

I'm not quite sure I understand what you're doing, but

wkday = format(11,"ddd")
isn't formatting the 11th of this month. It's formatting the 11th day after day
0. (Day 0 in VBA is 12/30/1899).

If you try:
(with theday = 11)
MsgBox Format(theday, "mm/dd/yyyy")
You'll see 01/10/1900

Option Explicit
Sub stuff()
Dim TheDate As Date
Dim TheMonth As Long
Dim TheDay As Long
Dim TheYear As Long
Dim WkDay As String

TheDate = Date
'not sure why you want the next two lines
'TheMonth = Month(TheDate)
'TheDay = Day(TheDate)
WkDay = Format(TheDate, "ddd")
MsgBox WkDay
End Sub

 

[Jon Peltier]

theDay is the day of the month, a simple numerical value, 11 (for 11 Jan
2007). Format(theDay, "ddd") accepts this number 11, and tells you "Wed".
But 11 is not today's date, the date corresponding to 11 is Wednesday,
January 11, 1900. The bug is in the way you used the functions one after the
other. If you instead use this line:

wkday2 = Format(theDate, "ddd")

you get the expected result of "Thu". But you should keep dates in date
variables, not in strings, because you never know how VBA might misinterpret
the text. Here's how you should rewrite your code, including declaring your
variables as the types you expect them to be, not what VBA arbitrarily
assigns:

Sub DateStuff()
Dim theDate As Date
Dim theDateString As String
Dim theMonth As Integer
Dim theDay As Integer
Dim wkday As String

theDate = Now
theDateString = Format(theDate, "mm-dd-yy")
theMonth = Month(theDate)
theDay = Day(theDate)
wkday = Format(theDate, "ddd")
End Sub

- Jon

 

[Dave Peterson]

Ps. WeekdayName was added in xl2k (or xl2002???):

MsgBox WeekdayName(Weekday(Date), abbreviate:=True)

 

[Jon Peltier]

Oops!

Day 11 in Excel is Wednesday, January 11, 1900.
Day 11 in VBA is Wednesday, January 10, 1900.

The difference is that VBA knows 1900 was not a leap year. Microsoft knew it
was a leap year when they created Excel, but for compatibility with Lotus
1-2-3, which didn't know from leap years (nor from business models
apparently), they pretended it was a leap year. But I got the Wednesday part
right.

- Jon