datetime conversion

[Roy]

Hi All,

I have a excel report in which I will get timestamp datatype as a
string and not datetime,which is not a required field and may be null
too.
if I pass 20090331221501, can I convert it to 2009-03-31@22:15:01. ?

Thanks,

Roy

 

[Gary''s Student]

=LEFT(A1,4) & "-" & MID(A1,5,2) & "-" & MID(A1,7,2) & "@" & MID(A1,9,2) & ":"
& MID(A1,11,2) & ":" & RIGHT(A1,2)

 

[Rick Rothstein]

Give this a try...

=--TEXT(A1,"0000-00-00 00\:00\:00")

 

[Rick Rothstein]

Whoops... you wanted it with the @ symbol....

=TEXT(A8,"0000-00-00\@00\:00\:00")

 

[Roy]

Rick,thanks for the solution.But if I want to use this in a module ,to
test the field for null and if it is not then format the field with
your formula,is that possible?How will the module be used?

 

[Rick Rothstein]

I'm not sure what you mean by "in a module" as this is a formula meant to be
placed in a cell on a worksheet. As for handling an empty cell (what you
called a "null field"), you would do it the standard way (test the cell's
contents in an IF function call and return "" if the cell is blank)...

=IF(A1="","",TEXT(A8,"0000-00-00\@00\:00\:00"))