View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Martin Fishlock Martin Fishlock is offline
external usenet poster
 
Posts: 694
Default 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?