Sign in to follow this  
Followers 0
Ram

Date format when writing to excel

7 posts in this topic

#1 ·  Posted (edited)

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 by Ram

Share this post


Link to post
Share on other sites



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. :)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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..!!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0