More Excel Date/Time splitting and converting

[m0raca]

You have all helped me out big time on my first AutoIT experience. I am now nearing the end of my project and have one final hurdle to vault. If any of you have the time I would very much so appreciate your input on where I am going wrong here.

I am trying to first _DateTimeSplit an excel value of "9/28/2008 1:32:28 PM". I only want the date to remain either in a new column in the same sheet or in a new workbook. The final trick is that the date has to be converted to a different format or it is useless. For example "9/28/2008" needs to be converted to "28-SEP-2008". I have read the readme, and gotten a function the gets close from many searches on the forum. This is what I have so far.

CODE

#include <Date.au3>

#include <Excel.au3>

;;;;Variables

$Y_instance = 2

$Y2_Date = 2

$sCellValue = 1

$sCell_Date = 1

;;;Code

$sFilePath1 = @ScriptDir & "\TestBook.XLS" ;This file should already exist

$oExcel = _ExcelBookAttach($sFilePath1)

If @error = 1 Then

MsgBox(0, "Error!", "Unable to Create the Excel Object")

Exit

ElseIf @error = 2 Then

MsgBox(0, "Error!", "File does not exist - Shame on you!")

Exit

EndIf

Dim $MyDate

Dim $MyTime

MsgBox(0, "", _DateSubtract(-1))

Func _DateSubtract($iSubtract)

Local $Days[7] = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" ]

Local $Month[12] = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]

$Split = StringSplit(_DateAdd('d', $iSubtract, _NowCalcDate()), "/")

$CurentDate = $Days[_DateToDayOfWeekISO($Split[1], $Split[2], $Split[3])] & ", " & $Month[$Split[2] - 1] & " " & $Split[3] & ", " & $Split[1]

Return $CurentDate

EndFunc

$sCellDate = _ExcelReadCell($oExcel, $Y_instance, 18) ;;Value 18=(or column R) to move the column

_DateTimeSplit($sCellDate, $MyDate, $MyTime)

$Year = $MyDate[1]

$Month = _DateToMonth($MyDate[2],0)

$Day = $MyDate[3]

MsgBox(0,"","Year: " & $Year & @CRLF & "Month: " & $Month & @CRLF & "Day: " & $Day)

It is a little messy, but I am still up from Friday here so please bear with me. $Y_instance = 2 and $sCellValue = 1 can be ignored as they are used in a different snippet of the code and are not relevant to the date conversion.

Thanks,

m0

[m0raca]

You have all helped me out big time on my first AutoIT experience. I am now nearing the end of my project and have one final hurdle to vault. If any of you have the time I would very much so appreciate your input on where I am going wrong here.

I am trying to first _DateTimeSplit an excel value of "9/28/2008 1:32:28 PM". I only want the date to remain either in a new column in the same sheet or in a new workbook. The final trick is that the date has to be converted to a different format or it is useless. For example "9/28/2008" needs to be converted to "28-SEP-2008". I have read the readme, and gotten a function the gets close from many searches on the forum. This is what I have so far.

CODE

#include <Date.au3>

#include <Excel.au3>

;;;;Variables

$Y_instance = 2

$Y2_Date = 2

$sCellValue = 1

$sCell_Date = 1

;;;Code

$sFilePath1 = @ScriptDir & "\TestBook.XLS" ;This file should already exist

$oExcel = _ExcelBookAttach($sFilePath1)

If @error = 1 Then

MsgBox(0, "Error!", "Unable to Create the Excel Object")

Exit

ElseIf @error = 2 Then

MsgBox(0, "Error!", "File does not exist - Shame on you!")

Exit

EndIf

Dim $MyDate

Dim $MyTime

MsgBox(0, "", _DateSubtract(-1))

Func _DateSubtract($iSubtract)

Local $Days[7] = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" ]

Local $Month[12] = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]

$Split = StringSplit(_DateAdd('d', $iSubtract, _NowCalcDate()), "/")

$CurentDate = $Days[_DateToDayOfWeekISO($Split[1], $Split[2], $Split[3])] & ", " & $Month[$Split[2] - 1] & " " & $Split[3] & ", " & $Split[1]

Return $CurentDate

EndFunc

$sCellDate = _ExcelReadCell($oExcel, $Y_instance, 18) ;;Value 18=(or column R) to move the column

_DateTimeSplit($sCellDate, $MyDate, $MyTime)

$Year = $MyDate[1]

$Month = _DateToMonth($MyDate[2],0)

$Day = $MyDate[3]

MsgBox(0,"","Year: " & $Year & @CRLF & "Month: " & $Month & @CRLF & "Day: " & $Day)

It is a little messy, but I am still up from Friday here so please bear with me. $Y_instance = 2 and $sCellValue = 1 can be ignored as they are used in a different snippet of the code and are not relevant to the date conversion.

Thanks,

m0

Well, maybe that sleep I got will make the difference. *bump*

[MrMitchell]

If you know the range...

$oExcel.ActiveSheet.Range("a1:c3").NumberFormat = "[$-409]d-mmm-yyyy;@"

Result: DD-MON-YYYY

Something like that?

[m0raca]

If you know the range...

$oExcel.ActiveSheet.Range("a1:c3").NumberFormat = "[$-409]d-mmm-yyyy;@"

Result: DD-MON-YYYY

Something like that?

I will try it here in a bit, letting the folders crystals soak in. If that just formats the cell and not the actual value in the cell then that is not needed.

[MrMitchell]

Yea it's just a format, try to read it out again and it will not be correct. You can do a simply copy paste and it will paste into notepad as displayed in Excel, but I'm not sure of any easier way yet to do that. Good luck!

[Juvigy]

maybe this is what you are looking for?

$test="9/28/2008 1:32:28 PM"

Dim $MyDate
Dim $MyTime
_DateTimeSplit($test,$MyDate,$MyTime)

For $x = 1 to $MyDate[0]
    MsgBox(0,$x,$MyDate[$x])
Next
For $x = 1 to $MyTime[0]
    MsgBox(0,$x,$MyTime[$x])
Next

[big_daddy]

Based off MrMitchell's example.

#include <Excel.au3>

$oExcel = _ExcelBookNew()
$oRange = $oExcel.ActiveSheet.Range("a1:a1")
With $oRange
    .Value = "9/28/2008 1:32:28 PM"
    .NumberFormat = "[$-409]d-mmm-yyyy;@"
    .Copy
EndWith
ConsoleWrite(ClipGet() & @CR)

[MrMitchell]

You can use what we've provided already (or use _ExcelNumberFormat) to format the date/time to display in DD-Mon-YYYY format while still preserving the entire date and time value for later calculations or whatnot. Use a modified version of _ExcelReadCell as another function in your script to read the text from the cell rather than the value.

Func _ExcelReadCellText($oExcel, $sRangeOrRow, $iColumn = 1)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Text
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Text
    EndIf
EndFunc   ;==>_ExcelReadCellText

Edited May 25, 2009 by MrMitchell