Excel Date Format

[SkysLastChance]

I am having a a heck of a time trying to get this format changed

Appointment Date
10/9/2021   8:05:58AM
10/12/2021   8:06:24AM
10/6/2021   8:19:13AM
10/12/2021   8:23:48AM
10/5/2021   8:27:36AM

I want it to look like this. 

Appointment Date
10/9/2021
10/12/2021
10/6/2021
10/12/2021
10/5/2021

Here is what I have tried. I can't even get it to format manually. to be honest. something about how the time is I am guessing. 

#include <Excel.au3>


Excel()




Func Excel() ;Attaches to excel

   Local $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.csv)")

   If FileExists($sExcelFile) Then
        $oExcel = _Excel_Open ()
        $oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen (*.csv) file.
   Else
        MsgBox(0,"No Excel File Found","Please locate excel file and try again")
        Exit
   EndIf

$oWorkbook.Activesheet.range("A2:A6").NumberFormat = "dd/mm/yyyy"

EndFunc

I could do this with a formula however, I would rather just format it. 

I have tried manually doing text to columns and still doesn't change. 

What am I doing wrong. What is going to be the simplest way to do this?  Am I going to have to edit the string and paste it back in? There has to be a simpler way. 

 

 

excel example.csv

Edited October 12, 2021 by SkysLastChance

[water]

Did you have a look at the wiki?
More details about formatting can be found here.

[SkysLastChance]

I did look at the Wiki. I think the problem is there is no space between the time and AM/PM. 

That seems to fix it. Not sure I can do anything with the time how it is. 

I am guessing I would have to do some type of search and replace.  Kind of annoying to be honest

Edited October 12, 2021 by SkysLastChance

[kjpolker]

It is because there no space between the AM/PM and the timestamp as you suggested. Are you open to solving this in Excel or only via AutoIt? Do you have a large amount of information and/or worksheets?

If wanting to use AutoIt I suggest getting the values of cells with information and inserting a space between the time stamp and AM/PM. This can be done with

_Excel_RangeRead()

_StringInsert () ;using negative values at the position will insert the string from the right. Luckily AM/PM is always 2 characters.

 

Edited October 12, 2021 by kjpolker

[SkysLastChance]

Yeah, I think it is going to be easier to fix the output of the report in this case instead of trying to work around it. 

Thanks all.

[water]

What you import from the file is a string, not a date. So you need  function to convert this string.
Something like this.

[Subz]

You could just replace "AM" with " AM" as well as the PM and then format, example:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\excel example.csv")
$oWorkbook.ActiveSheet.Columns("A:A").Select
$oExcel.Selection.Replace("AM", " AM")
$oExcel.Selection.Replace("PM", " PM")
$oExcel.Selection.NumberFormat = "m/d/yyyy"

 

[seadoggie01]

I highly suggest avoiding .Select and .Selection in VBA/COM whenever possible for performance, maintainability, and more. You can write it like this instead:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\excel example.csv")
Local $oRange = $oWorkbook.ActiveSheet.Columns("A:A")
$oRange.Replace("AM", " AM")
$oRange.Replace("PM", " PM")
$oRange.NumberFormat = "m/d/yyyy"