rearrange date field output

[Cabinetman]

I need to take a date field that is not always today's date (example: 8/1/2009) and change the output to yyyy-mm-dd

I'm trying to use a calc field as text since I get a "?" if I use it as date:

Let ( [y = Right ( date ; 4 ) ;m = Left ( date ; 2 ) ;d = Middle ( date ; 4 ; 2 )] ; Substitute ( y & "-" & m & "-" & d ; "/" ; "-" ))

However since the date field isn't 08/01/2009 I can't get a consistent output.

I looked at some custom functions but can't quite find what I need ...

I think I'm close but maybe I'm going about this wrong? Help is appreciated.

Edited August 9, 2009 by Guest

[comment]

Use the date functions - Year(), Month () and Day() - to extract individual date elements, instead of text functions like Left() and Middle().

[Lee Smith]

You also do not need the Substitute function,

Try

Let (

[y = Year ( Date ) ;

m = Right ( 0 & Month ( Date ); 2) ;

d = Right ( 0 & Day ( Date ) ; 2)] ;

y & "-" & m & "-" & d )

HTH

Lee

[Cabinetman]

I'm getting a "?" with your calc.....

Edit: Spoke to soon. When I changed the calc output to text instead of date it's now ok... thanks

Edited August 9, 2009 by Guest