Jump to content

Excel Date Format


Go to solution Solved by Subz,

Recommended Posts

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

Life's simple. You make choices and you don't look back.

Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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

Life's simple. You make choices and you don't look back.

Link to post
Share on other sites

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 by kjpolker
Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • Solution

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"

 

Link to post
Share on other sites

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"

All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...