Date format using = FormatDateTime(Now, vbGeneralDate)

[alchohaz]

Hi there hopefully this will be a simple one

I have the following line of Code:

    Range("Completed").Value = FormatDateTime(Now, vbGeneralDate)

It sets a time and date stamp to CELL Named "Completed" - last week this working fine, but this week, strangley the date has changed to the US Standard.

Example:

Todays date is stamped as 11/02/10 etc when i would expect it to be 02/11/10 etc - Is this set in the code or the APP Settings itself?

Thanks in advance

Al

 

[PHV]

Why storing a text value for a date ?
Range("Completed").Value = Date

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Check out faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alchohaz]

Thank for your comments - neithet have addressed my issue. The Code i have used was working fine until some point last week, and now it's gone all "US standard" on me. I don't want to change the code really, and i have checked the regional settings and they seem OK.

 

[PHV]

So, what about this ?
Range("Completed").Value = Format(Now, "dd/mm/yy")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alchohaz]

Thanks PHV - that will work on my code but i want the hours mins and seconds too, i am guesing i can just add that as if it were a custom date/time - i will have a play to resolve that.

However it doesn't fix my overall problem which is also reflected when i enter a date using a form i have created.

Example when i enter 08/11/10 in the form, it is resolved in the sheet as 11/08/10 (August rather than November). I am assuming the two issues (The Form in put and the vbGeneralDate) are related, Regional Setting seems the obvious choice, but problem is not apparent when i checked that area.

 

[alchohaz]

Something very suspect happening here - i changed the regional settings to US and ran the Macro - this resulted in the correct information being displayed !! I changed it back to UK setting ran the macro again and they are now incorrect !!

Any tips please ?

 

[SkipVought]

Post the macro code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alchohaz]

Code is as above in the following context:

If Application.Dialogs(xlDialogSendMail).Show("abc.t.com  ", Subject) = True Then
    Range("Completed").Value = FormatDateTime(Now, vbGeneralDate) 

End If

But i have now managed to get it to work as follows:

If Application.Dialogs(xlDialogSendMail).Show("abc.t.com  ", Subject) = True Then
    Range("PM_APP").Value = Format(Now, "DD/MMM/YYYY HH:MM:SS")
End If


    Range("PM_APP").Select
    Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"

However the original issue still remains, i have just worked around it.

 

[SkipVought]

You have not read my FAQ!

You have assigned a STRING to Range("PM_APP"), not a DATE!

If Application.Dialogs(xlDialogSendMail).Show("abc.t.com  ", Subject) = True Then
    With Range("PM_APP")
        .Value = Now
        .NumberFormat = "dd/mm/yyyy hh:mm:ss"
    End with
End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alchohaz]

Skip i did, but to me as a novice this was not clear - thanks for your input, it is always appreciated.

In my defence what i was actually after was the cause of the issue - this was working last week (as Value = FormatDateTime(Now, vbGeneralDate) and now for some unknown reason it has changed to the US Format with no rhyme or reason.

On that particular foible it loks like i won't find out the reason (if there is one) - but thank you all for your input anyway - am taking on board all of the tips and tricks as i go along

cheers
Al

 

[SkipVought]

Did it work for you?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alchohaz]

Skip - i have it working - thank you