Date format when writing to excel

[Ram]

I am writing a script so that I get DOB and Time from array and I need to write to excel. how do I go about doing this?

For eg: The actual date format: 6/22/70 18:40:00

When I put it to array and call a message box I get this format: 19700622184000

and I put the same array to a row in excel say for eg: "A5" when I do that it writes like this 1.97006E+13 to Column A, 5th row.

Can you tell me how do I go about changing the format to suit the actual format : 6/22/70 18:40:00?

Awaiting your response!

Thanks!

Edited August 19, 2007 by Ram

[DjDeep00]

I am writing a script so I get a DOBandTime from array and I need to write to excel. how do I go about doing this?

For eg: The actual date format: 6/22/70 18:40:00

When I put it to array and call a message box I get this format: 19700622184000

and I put the same array to a row in excel say for eg: "A5" when I do that it writes like this 1.97006E+13 to Column A, 5th row.

Can you tell me how do I go about changing the format to suit the actual format : 6/22/70 18:40:00?

Awaiting your response!

Thanks!

Posting some of your code would make it much easier.

[Ram]

Posting some of your code would make it much easier.

I haven't written much in the code

;excel file that has the date
$oExcel = _ExcelBookOpen("C:\test.xls") 

;array that has the date taken from excel file
$aColumnB  = _ExcelReadArray ($oExcel, 1, 2,  7, 1)

; new excel file
Local $oExcel = _ExcelBookNew(1)


_ExcelWriteCell($oExcel, "DOB/Time", "B2")
MsgBox (0, "Test", $aColumnB[2])
_ExcelWriteCell($oExcel, $aColumnB[2], "B3")

And I get the format that I told you.

[DjDeep00]

I haven't written much in the code

;excel file that has the date
$oExcel = _ExcelBookOpen("C:\test.xls") 

;array that has the date taken from excel file
$aColumnB  = _ExcelReadArray ($oExcel, 1, 2,  7, 1)

; new excel file
Local $oExcel = _ExcelBookNew(1)
_ExcelWriteCell($oExcel, "DOB/Time", "B2")
MsgBox (0, "Test", $aColumnB[2])
_ExcelWriteCell($oExcel, $aColumnB[2], "B3")

And I get the format that I told you.

Ram...What I would do is open the excel file that you are reading the value from and make the formatting of that cell just simple "text" and then you should have no problem reading it using your code.

[Ram]

Ram...What I would do is open the excel file that you are reading the value from and make the formatting of that cell just simple "text" and then you should have no problem reading it using your code.

Yep. that is easy but the problem is that i have few calculation done using the date format in the excel so can't change the format to text. Any other way?

[DjDeep00]

Yep. that is easy but the problem is that i have few calculation done using the date format in the excel so can't change the format to text. Any other way?

This is a quick work around....

#include <ExcelCOM_UDF.au3> 

$Excel_File=@ScriptDir & "\Date.xls"

$Excel_Obj=_ExcelBookOpen($Excel_File,0)

$Fixed_Date=Date_Format(_ExcelReadCell($Excel_Obj, 1,1))

MsgBox(4096,"","Before: 6/22/70 18:40:00" & @CRLF & @CRLF & "After: " & $Fixed_Date)

_ExcelBookClose($Excel_File,0)


Func Date_Format ($String)

$Year=StringLeft($String,4)
$Month=StringLeft(StringTrimLeft($String,4),2)
$Day=StringLeft(StringTrimLeft($String,6),2)

$Hour=StringRight(StringTrimRight($String,4),2)
$Min=StringRight(StringTrimRight($String,2),2)
$Sec=StringRight($String,2)

$Date=$Month & '/' & $Day & '/' & $Year
$Time=$Hour & ':' & $Min & ':' & $Sec

Return $Date & ' ' & $Time

EndFunc

[Ram]

This is a quick work around....

#include <ExcelCOM_UDF.au3> 

$Excel_File=@ScriptDir & "\Date.xls"

$Excel_Obj=_ExcelBookOpen($Excel_File,0)

$Fixed_Date=Date_Format(_ExcelReadCell($Excel_Obj, 1,1))

MsgBox(4096,"","Before: 6/22/70 18:40:00" & @CRLF & @CRLF & "After: " & $Fixed_Date)

_ExcelBookClose($Excel_File,0)
Func Date_Format ($String)

$Year=StringLeft($String,4)
$Month=StringLeft(StringTrimLeft($String,4),2)
$Day=StringLeft(StringTrimLeft($String,6),2)

$Hour=StringRight(StringTrimRight($String,4),2)
$Min=StringRight(StringTrimRight($String,2),2)
$Sec=StringRight($String,2)

$Date=$Month & '/' & $Day & '/' & $Year
$Time=$Hour & ':' & $Min & ':' & $Sec

Return $Date & ' ' & $Time

EndFunc

Thanks DjDeep00.. This helped me out.. Now I am getting the format correctly..!!