Problem with the format of dates in VBA when declaring "As Date"

[SeanE]

Good evening all.

I was initially going to post a question about an issue with formatting dates in VBA, when adding 7 days onto the Date, but I've kind of solved the problem myself, which led to something else.

DIM afDate As Date
afDate = Format(Date + 7, "mm/dd/yyyy")

The above code would not work for me. No matter what I tried, afDate would always seem to return the date in the dd/mm/yyyy format, which is what my system is set to.

Whilst writing the post, though, I discovered that removing "DIM afDate As Date" rectified the problem.

My question now is why? Why will the code not word if I Dim afDate As Date? I would like to use Option Explicit, which means I need to declare afDate, though if I guess I'll have to leave it out if necessary.

Using Excel 2003 & Vista

Thank you for your time.

Sean

 

[Norie]

Sean

afDate isn't really being formatted.

When you output afDate it's output in the system date format because it is a date.

The use of Format isn't really doing anything, it will convert Date to a string in the reuqired format but that will be converted to a 'real' date when you assign
it to the date variable.

What are you going to use afDate as/for later in the code?

 

[SeanE]

Norie, thank you for your reply.

It odd that you should say that, because the following code does format afDate correctly, as I would expect it (unless I have misunderstood you).

Dim afDate As Date
Format(Date, "mm/dd/yyyy")

This is the same as the previous code, except without the + 7 days. In this, afDate is ouput in the required format, i.e. 07/10/2011 (instead of 10/07/2011).

afDate is used for an AutoFilter. I have two subs and my main goal is to apply a filter to a sheet, with one sub filtering all rows with todays days (<=Date), and the second filtering rows with a date within the next seven days (<= Date + 7).

The dates are stored in the sheet as dd/mm/yyyy, but unless I format Date to an American format, the filter doesnt work.

Thank you again.

 

[John_w]

The Format function returns a string, so afDate should be declared as a string, i.e.

Dim afDate As String

 

[shg]

You don't need Format at all. A date in Excel is a number, it has no format until you represent it with a string.

Dim afDate As Date
afDate = Date + 7

 

[SeanE]

Brillitant, John.

You know, it seems so obvious now...

Thank you both

 

[SeanE]

shg, thats exactly what I would have thought, but as explained in my previous post, for some reason the AutoFilter wouldn't find the relevant records unless I formatted the date.

It was only after hours of searching I came across this thread (third post down) and after formatting the date, got it to work without any problems. It was only when I tried to add days onto the date that I encounted issues.

I've changed afDate to a string now it its working perfectly.

Thanks

 

[Norie]

Sean

Using dates in VBA/Excel is always tricky.

Your code is actually converting from date to string, via the Format function, then back to date when you assign to the variable.