Date# Formating conversion issue?

[#1]

Im trying to format my date to look like this: ,'DD/MM/YYYY'

I thought my input mask was correct, but it won't work. Please help

=Date(Date#(ActualStart,'YYYY-MM-DD h:mm:ss TT'),'DD/MM/YYYY')

Raw Input Dates

2012-12-12T17:00:00

    2012-12-14T17:00:00

    2012-12-20T17:00:00

    2012-12-21T17:00:00

    2013-01-02T08:00:00

    2013-01-02T17:00:00

    2013-01-08T17:00:00

    2013-01-18T17:00:00

    2013-01-21T17:00:00

    2013-02-25T17:00:00

    2013-02-27T17:00:00

    2013-03-01T17:00:00

    2013-03-05T08:00:00

    2013-03-05T17:00:00

    2013-03-06T17:00:00

    2013-03-07T17:00:00

    2013-03-11T17:00:00

    2013-03-21T17:00:00

[rbecher]

Hi Brain,

first replace the T between date and time and then use the mask 'YYYY-MM-DD hh:mm:ss'.

- Ralf

[#3]

I tried this but no luck: I inserted the 'T' to match the input date .... 2013-03-21T17:00:00

=Date(Date#(ActualStart,'YYYY-MM-DDThh:mm:ss'),'DD/MM/YYYY')

[chematos]

You should do this in load script.

=Date(Date#((Replace(ActualStart,'T',' '),'YYYY-MM-DD h:mm:ss TT') ,'DD/MM/YYYY')

[#5]

Good idea. I was totally expecting this too work. It rendered it null?   Must be input mask?

Date(Date#(Replace(ActualStart,'T',' '),'YYYY-MM-DD h:mm:ss TT') ,'DD/MM/YYYY') as ActualStart,

[rbecher · Accepted solution]

Why the TT?

Date(Date#(Replace(ActualStart,'T',' '),'YYYY-MM-DD hh:mm:ss') ,'DD/MM/YYYY') as ActualStart,

[chematos]

Right, delete TT ...

While you are loading the table at the script, use the expression above to format ActualStart field, you can use the same name or duplicate the field renaming with other name field and keeping the original if you want to compare both of them to see if the result is ok.

Hope this helps.

[#8]

This portion works. But the Date() wont....

Date#(Replace(ManualStart,'T',' '),'YYYY-MM-DD hh:mm:ss TT')

[chematos]

Try:

Date(Date(Replace(ManualStart,'T',' '),'YYYY-MM-DD hh:mm:ss') ,'DD/MM/YYYY')

Maybe this works:

Date(Replace(ManualStart,'T',' '),'DD/MM/YYYY')

or:

Date#(Date#(Replace(ManualStart,'T',' '),'YYYY-MM-DD hh:mm:ss') ,'DD/MM/YYYY')

I'm not sure what's the result using Date# or Date, sometimes works in a different and unexpected way...

[#10]

I copied it from the Default mask but your right no need for it. I removed after the fact.....