Sign in to follow this  
Followers 0
m0raca

More Excel Date/Time splitting and converting

8 posts in this topic

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

Share this post


Link to post
Share on other sites



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*

Share this post


Link to post
Share on other sites

If you know the range...

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

Result: DD-MON-YYYY

Something like that?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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

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