How do you force the way VBA interprets dates.

[Mightyginger]

Say I have a function called Bob which has one parameter fed into it, a date.

Function Bob(whichformat As Date)

do some operations

End Function

My problem is that my country settings are UK and the date is in UK format but then interpret the date as US if it can be intepreted in that format or it'll swap the month and day and then use that as the date. My work around at the moment is to input the day,month and year as integers and then use the Date function.

To the point. Question is, is it possible to explicitly say to vba whichformat As Date BUT READ THIS ALWAYS AS A U.S. DATE FORMAT? So that if different people use it they just need to type the date in US format and the code will always be expecting US format.

Not looking for masses of code, just thought there might be a command tucked away somewhere with which I could do this.


Cheers,



Neil.

 

[nath]

Hi Neil,

I suppose the safest way would be to pass the parameter as string (forcing a very rigid format), decompose the parts and put it back together in any format you like. Something like:

 a = "01/03/2003"
    MsgBox Format(DateSerial(Right(a, 4), Left(a, 2), Mid(a, 4, 2)), "dd mmm yyyy")

Hope this helps,

Nath

 

[Mightyginger]

Hi Nath,

Yeah, thanks for replying, but you see thats manipulating the date after it's been passed into vba. So the damage has already been done - vba has already read the date in incorrectly by this point. I know what you were thinking though - LOL - I've tried similar stuff but realised that the problem is when vba initially gets the date.

Any other suggestions? Could be an easter egg in this for someone


Neil.

 

[bryanbayfield]

When you say "VBA has already read the date in incorrectly by this point" do you mean that the dates are being read incorrectly from cells in a worksheet ... or what?!

 

[Mightyginger]

If I have a cell that has a date in it I then pass that into a vba fuction as a parameter. When I do this at that point VBA is determining what the date is - it assumes that it's in US regardless of the country settings and so something like 01/01/03 it'll keep like that but it'll read 02/01/03 from a UK formatted cell as 1st Feb 03 and VBA will read 29/01/03 as me making a mistake, it'll change it and put it as 01/29/03.

 

[nath]

Well Neil,

it all depends when and how the user passes the date. Is it through a textbox, a text file, something else ... do you have any access to it before it gets to your function?

You must have access to the first entrypoint somehow, otherwise it is too late. If you feed a date in your PC, it reads it accordingly to the specs of your PC.

Maybe some more contextual information might help.

Nath

 

[Mightyginger]

Cheers Nath. It's in a cell. Just fed in like that.

 

[combo]

If the date in the cell is correct (i.e. recognized by excel as date or serial number) - when you refer to it by the value of the cell - VBA will get proper value, whatever local settings or date formatting is.

combo

 

[nath]

Sorry was writing answer at same time as you Neil.

Well according to me, the way a date is interpreted in a cell is the same as in VBA and depends on the regional settings, and the way it is displayed is a simple matter of formatting.

I did the following test in regional settings UK: In the cell I put 1st of march in 3 different formattings (UK, US and dd-mmm-yy). Then I create a function that simply adds 1 day:

Function add1(dd As Date)
    add1 = dd + 1
End Function

I apply the function to each of my 3 cells: they all were modified properly to the 2nd of March, each in their respecting formatting. I then changed to US settings and it worked fine as well.

So what is ther in your function that makes it fail?

Nath

 

[bryanbayfield]

Have you tried:

WorksheetFunction.Text([A1], "dd/mm/yy")

where your date is in A1?

 

[Mightyginger]

yeah, got a variation of that. I'll see what I can do with it. Cheers.