Convert 'DD/MM/YYYY hh:mm AM' into date

[Anonymous]

please help me to convert 13/10/2017 09:10 AM into date ( day,'DD/MM/YYYY hh:mm')

- not working =date(date#('13/10/2017 09:10 AM' ,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')

[its_anandrjs]

That was typo you have to use Timestamp rather than date because you have Time in your date format then try with timestamp

Try with this and check

=Timestamp(Timestamp#('13/10/2017 09:10 AM' ,'DD/MM/YYYY hh:mm TT'),'DD/MM/YYYY hh:mm')

[sunny_talwar · Accepted solution]

Try this

=Date(Date#('13/10/2017 09:10 AM' ,'DD/MM/YYYY hh:mm TT'),'DD/MM/YYYY hh:mm')

[swuehl]

And if you want to convert the timestamp into a date, don't forget to stripp of the time part:

=Date(Floor(Date#('13/10/2017 09:10 AM' ,'DD/MM/YYYY hh:mm TT')),'DD/MM/YYYY')

edit: added missing single quote and moved parenthese

[sunny_talwar]

Missed a single quote at the end of the format

[its_anandrjs]

Another way is

Load

*,

Timestamp(Timestamp#( Left(DateFormat,16),'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as NewFormat2,

Timestamp(Timestamp#(DateFormat ,'DD/MM/YYYY hh:mm TT'),'DD/MM/YYYY hh:mm') as NewFormat;

Load * Inline

[

DateFormat

"13/10/2017 09:10 AM"

];

[sunny_talwar]

This is risky....

Timestamp(Timestamp#( Left(DateFormat,16),'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as NewFormat2,

Because if you do this, then how will you differentiate between morning 8 or evening 8?

[its_anandrjs]

May be with AM and PM though then in final format AM / PM should be there.

[sunny_talwar]

Yes, the second one is fine, but just wanted to point out that the one where you use left() function will always think that the date is in AM.