date time conversion

[Former Member]

Someone has exported a CSV file with Date Time field sent as text with 'period' separation. How can I convert from 12.10.2009 10:00:00 to a recognised date time for future date difference calculation?

Regards,

Brian.

[Former Member · Accepted solution]

hi,

In a formula field try out this

cdatetime(TONUMBER(MID(Fieldname,7,4)),

TONUMBER(MID(Fieldname,4,2)),

TONUMBER(MID(Fieldname,1,2)),

TONUMBER(MID(Fieldname,12,2)),

TONUMBER(MID(Fieldname,15,2)),

TONUMBER(MID(Fieldname,18,2)))

Regards

Sathish

[Former Member · Accepted solution]

Thanks for response. I tried that and in came back with errors. I should have said that I am looking for date only.

If you can help, it would be appreciated. I am pretty lost when it comes to extarcting info from text fields from CSV files.

Regards,

Brian.

[Former Member · Accepted solution]

Not sure if the date in the string field is MM.DD.YYYY or DD.MM.YYYY from your example but here is a formula that you can use.

cdate(replace(split(x)[1],'.','/'));
// where x is the string field that contains date time

Edited by: Sanjay Kodidine on Apr 17, 2009 7:55 AM

[former_member260594 · Accepted solution]

You shouldn't even need to use the split function just cdate( replace( , '.', ','))

[Former Member]

Hi

Try the following formula :

Numbervar hh;

Numbervar mm;

Numbervar ss;

hh:=tonumber(Mid(,1,2)); mm:=tonumber(Mid(,4,2));

ss:=tonumber(Mid(,6,2));

Time(hh,mm,ss)

Thanks,

Sastry

[Former Member]

Thanks for quick response. However, I should have said that I am not interested in the time elements only need to extract a date that I can use. If you can help, I would appreciate that.

Brian.