datetime conversion

  • Thread starter Thread starter Roy
  • Start date Start date
R

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
 
=LEFT(A1,4) & "-" & MID(A1,5,2) & "-" & MID(A1,7,2) & "@" & MID(A1,9,2) & ":"
& MID(A1,11,2) & ":" & RIGHT(A1,2)
 
Whoops... you wanted it with the @ symbol....

=TEXT(A8,"0000-00-00\@00\:00\:00")
 
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?
 
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"))
 
Back
Top