SkysLastChance

Format Cell as Date *3/14/2001

3 posts in this topic

#1 ·  Posted (edited)

Is there an easy way to format a cell to date or currency? I found some old threads that were not much help. 

I have looked at the wiki but I only see how to format as a number

I assume there is not a way like I did for making the text bold. 

Any suggest or help would be appreciated. 

 

 

I am able to get the format changed to text using

$oExcel.Activesheet.range("A1:L1").NumberFormat = "@"

 

#include <Excel.au3>

Global $sBox

Func InputHowMany()

    While 1
        $sBox = Number(InputBox("How many?", "How many?"))
        If $sBox = 0 Then
               $iMsg = MsgBox(1, 'Oops', 'Please enter a valid number')
            If $iMsg = 2 Then Exit
        Else
            Return $sBox - 1
        EndIf
    WEnd
 EndFunc

InputHowMany()
$oExcel = _Excel_Open()
$oExcel = _Excel_BookNew($oExcel)

Local $t = 2
Local $w = 1
Local $c = 301

$oExcel.Activesheet.range("A1:L1").font.bold = True


Do
   _Excel_RangeWrite($oExcel,Default, "Status", "A1")
   _Excel_RangeWrite($oExcel,Default, "Last Name", "B1")
   _Excel_RangeWrite($oExcel,Default, "Last Name", "C1")
   _Excel_RangeWrite($oExcel,Default, "SSN", "D1")
   _Excel_RangeWrite($oExcel,Default, "DOB", "E1")
   _Excel_RangeWrite($oExcel,Default, "Email", "F1")
   _Excel_RangeWrite($oExcel,Default, "Mailing Address", "G1")
   _Excel_RangeWrite($oExcel,Default, "City", "H1")
   _Excel_RangeWrite($oExcel,Default, "State", "I1")
   _Excel_RangeWrite($oExcel,Default, "Zip Code", "J1")
   _Excel_RangeWrite($oExcel,Default, "Gender", "K1")
   _Excel_RangeWrite($oExcel,Default, "Phone", "L1")

   _Excel_RangeWrite($oExcel,Default, '=B' & $c, "B" & $t)
   _Excel_RangeWrite($oExcel,Default, '=C' & $c, "C" & $t)
   _Excel_RangeWrite($oExcel,Default, '=D' & $c, "D" & $t)
   _Excel_RangeWrite($oExcel,Default, '=E' & $c, "E" & $t)
   _Excel_RangeWrite($oExcel,Default, '=F' & $c, "F" & $t)
   _Excel_RangeWrite($oExcel,Default, '=G' & $c, "G" & $t)
   _Excel_RangeWrite($oExcel,Default, '=H' & $c, "H" & $t)
   _Excel_RangeWrite($oExcel,Default, '=I' & $c, "I" & $t)
   _Excel_RangeWrite($oExcel,Default, '=J' & $c, "J" & $t)
   _Excel_RangeWrite($oExcel,Default, '=K' & $c, "K" & $t)
   _Excel_RangeWrite($oExcel,Default, '=L' & $c, "L" & $t)

      $c = $c + 1
      $t = $t + 1
      $w = $w + 1

Until $w > $sBox

 

 

 

Edited by SkysLastChance

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

Share this post


Link to post
Share on other sites



#2 ·  Posted

You should be able to do

$oExcel.Activesheet.range("A1:L1").NumberFormat = "yyyy-mm-dd"

For that specific date format.

1 person likes this

UHJvZmVzc2lvbmFsIENvbXB1dGVyZXI=

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

The link I posted in the wiki for numbers contains examples for date format as well: http://peltiertech.com/Excel/NumberFormats.html

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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

  • Similar Content

    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi all,
      i have an empty csv file, i have a non formatted text file.
      What do i want to do?
      I want to automate the process "get external data" in Excel, i want to import the data from the text file and basically create a csv file with a specific character encoding.
      Is it possible with AutoIT?
       
    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd  
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.