write date into file in YYYY.MM.DD format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lars Schouw - 28 May 2008 13:09 GMT

From VBA I want to write a date into a file a file in the format
YYYY.MM.DD
how can I do this?

Lars

Reply to this Message

Ron de Bruin - 28 May 2008 13:24 GMT

Hi Lars

Why not use the ISO format
http://www.rondebruin.nl/isodate.htm

1. Dates must be represented in either:
a. non-separated form of yyyymmdd (eg 20050330 for 30-Mar-2005)
b. separated form of yyyy-mm-dd (eg 2005-03-30 for 30-Mar-2005)

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> From VBA I want to write a date into a file a file in the format
> YYYY.MM.DD
> how can I do this?
>
> Lars

Reply to this Message

Lars Schouw - 28 May 2008 13:51 GMT

Thanks Ron,

=TEXT(TODAY(),"yyyy.mm.dd")

seems to do the stunt..

I need this format since some application have depends on it.. I agree
ISO format is much nicer,

Reply to this Message

Ron de Bruin - 28 May 2008 14:03 GMT

Look out if you also use Non English systems if you use the Text function

yyyy in the Netherlands is jjjj

English language Version entry:
="Today is "&TEXT(TODAY(),"yyyy-mm-dd")

In English language version the formula returns:
Today is 2005-02-23

Fails when the when workbook is open in Dutch language version (year = jaar "jjjj-mm-dd").
With a Dutch language version the formula will return:
Today is yyyy-02-23

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Thanks Ron,
>
[quoted text clipped - 4 lines]
> I need this format since some application have depends on it.. I agree
> ISO format is much nicer,

Reply to this Message

Lars Schouw - 29 May 2008 07:14 GMT

Thanks I sometimes have users on Japanese systems.
Is there a nice workaround for that problem ?
Lars

> Look out if you also use Non English systems if you use the Text function
>
[quoted text clipped - 24 lines]
>
> - Show quoted text -

Reply to this Message

Ron de Bruin - 29 May 2008 17:23 GMT

Hi Lars

If you do it with VBA it is always working correct

Sub test()
ActiveCell.Value = Format(Date, "yyyy.mm.dd")
End Sub

But not with the worksheetfunction

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Thanks I sometimes have users on Japanese systems.
Is there a nice workaround for that problem ?
Lars
On May 28, 10:03 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:

> Look out if you also use Non English systems if you use the Text function
>
[quoted text clipped - 25 lines]
>
> - Show quoted text -

Reply to this Message