Date into workable format?

[cmccafferty]

Hi All,

Can someone advise as to where I am going wrong with this:

=date([BD ReturnDate], ('DD/MM/YYYY'))

When download a table into excel with that in it, I get the date showing correctly, but I have to do Text-to-Columns if I want to be able to sort and filter it properly.

Thanks

Chris

[sunny_talwar]

You have an extra set of parenthesis around the date format... I would remove them and see if that works

=Date([BD ReturnDate], 'DD/MM/YYYY')

[YoussefBelloum]

And if the above solution don't work, you can try this:

=Date(Date#([BD ReturnDate], 'DD/MM/YYYY'))

or this:

=Date(Date#([BD ReturnDate], 'DD/MM/YYYY'),'DD/MM/YYYY')

[cmccafferty]

Tried all 3, none worked unfortunately.

Is my load script okay...?

[YoussefBelloum]

the script seems to be ok, but the rest of the Date fields you're formatting, formatting works for these fields ?

[cmccafferty]

Yep, they work fine.

I tried changing the script so that [BD ReturnDate] is in the same way. But still no joy

[YoussefBelloum]

would you be able to show us via a sample app or an image what you have so far?  using that field in a listbox for example

[Miguel_Angel_Baeyens]

Note that in the expression:

Date(Date#([BD ReturnDate], 'DD/MM/YYYY'),'DD/MM/YYYY') AS [BD ReturnDate]

The first date mask (red) is the input mask (the one in which the value of the field is stored) and the second one (blue) the output mask (the one in which you want to display the value. They may not be equal if the input data is in American format, e.g.: M/D/YYYY, in which case, the expression would look like

Date(Date#([BD ReturnDate], 'M/D/YYYY'),'DD/MM/YYYY') AS [BD ReturnDate]

[cmccafferty]

UPDATE...

Having changed the script, I just changed the table from =date([BD ReturnDate], ('DD/MM/YYYY'))

to simply [BD ReturnDate]

Thanks for all your help and suggestions.

[sasiparupudi1]

Is it working fine? if so, pl close this thread by marking any helpful and a correct answer.

if not

may be try breaking the date into

Month([BD ReturnDate])

Year([BD ReturnDate])

see if that woks