Text calc to reformat date

[dbhill]

To use a date expression in an exported PDF filename I would like to convert a date field (i.e.; 10/16/2005) into a text expression (i.e.; 051016) so that the PDF file will sort properly in a directory list. Is there a way to do this with a text calculation?

Any help on this is greatly appreciated.

~Dennis

[Ender]

Something like this should work:

right(year(Date);2) & right("0" & month(Date);2) & right("0" & day(Date);2)

[aldipalo]

Will this help?

Calc field>return as text:

Month(::DateField) & Day(::DateField) & Year (::DateField)

I use a similar approach to save as pdf as well as send a pdf as attachment to an email.

I have the date conversion field and a ::PdfFileName calc field.

Then concatenate the fields

Calc = "C:Documents and SettingsAlMy DocumentsFilemaker CMpdf FilesJobs" & ::DateField & ".pdf"

*Of course if you are using a MAC your filename protocol is different than Windows XP.*

Works fine for me.

HTH

Al

******************

Hey Ender:

I'll always bow to your experience. To also continue the learning process is there anything wrong with the way I laid this out? Is it open to possible problems or just another way to approach the problem?

AL

Edited September 12, 2006 by Guest

[Ender]

Hey Al,

Month(::DateField) & Day(::DateField) & Year (::DateField)

Unfortunately, this would not sort correctly in an alphabetical listing. For example, your calc would take dates:

1/31/2005

2/12/2005

12/1/2005

1/31/2006

and result in:

1312005

2122005

1212005

1312006

or sorted:

1212005

1312005

1312006

2122005

This is why the order is changed to YYMMDD, and why it is necessary to pad any single-digit months and days.

[Ender]

BTW: If you don't want any millennium bugs, the year should really be four digits. Otherwise "99" years will sort after "05" years.

[dbhill]

Ender wins the prize; this works very nicely... I'm so happy to have this solution to my perplexity.

btw, the millenium bug does not apply here, since the date range is within the last three years, so your original formula is perfect for this application. I am most appreciative.

~Dennis

[aldipalo]

I see what you are saying and it makes sense. If I am using this as a stand alone and will be performing sorts it will be best. And I really didn't think about the Millennium issue. I'll have to look at all of my date fields and calculations and make sure I haven't left a bomb in there somewhere.

That's what I love about this forum, every time I come on I learn something new. Who said you can't teach an old dog new tricks. :

Al

[dbhill]

I have to say, Ender, that the thing I like best about your calc is the pad for the single-digit days and months. This makes the directory sort work perfectly. Thanks again.

~Dennis