Wierd Bug with Format Date VBA???
 

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?

Wierd Bug with Format Date VBA???
 

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

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?

Wierd Bug with Format Date VBA???
 

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




wrote:

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?

--

Dave Peterson

Wierd Bug with Format Date VBA???
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ps.com...
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?

Wierd Bug with Format Date VBA???
 

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

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



Dave Peterson wrote:

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

wrote:

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?

--

Dave Peterson

--

Dave Peterson

Wierd Bug with Format Date VBA???
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


wrote in message
ps.com...
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?