# Excel Date Format Change with Vbscript



## Squashman (Apr 4, 2003)

This is a continuation from this previous thread I started but is more specific to changing one more thing before I export the worksheet to a tab delimited file.

Need to add one more thing to this script and it has to do with the way the date is formatted in some of the cells. The cell gets exported as to what the cell format is versus what the true cell value is.

So for instance. The actual cell value that is typed into the cell is 12/23/2013 but the client put in a custom format of d-mmm so it displays as 23-Dec and that is how it gets exported to the tab delimited file. I need the cell to export to the tab delimited file as 12/23/2013.

So I tried this code to change the Date Formatting but this did not work.

```
If IsDate(rCell.Value) Then
     rCell.Value = DateValue(rCell.Value)
End If
```
Anyone else got any ideas.


----------



## Squashman (Apr 4, 2003)

Well this is odd. I decided to print the cell value every time it finds a date and sure enough it prints the date correctly to the screen: 12/23/2013
Apparently I am not changing the correct value within the cell.


----------



## Squashman (Apr 4, 2003)

Was looking at his code on StackOverFlow but not sure how to implement it because I want it to go through each cell and check if it is a date before it applies the new number format. This code has hard coded columns.

```
Dim objXLApp, objXLWb, objXLWs

Set objXLApp = CreateObject("Excel.Application")

objXLApp.Visible = True

Set objXLWb = objXLApp.Workbooks.Open("E:\Docs\Invoice.csv")

'~~> Working with Sheet1
Set objXLWs = objXLWb.Sheets(1)

With objXLWs
    .Columns("G:G").NumberFormat = "m/d/yyyy"
    .Columns("H:H").NumberFormat = "0.00"
End With
```
My current block of code.

```
For Each rCell In objSheet.UsedRange
	On Error Resume Next
	If IsDate(rCell.Value) Then
		rCell.Value = DateValue(rCell.Value)
	End If					
Next
```


----------



## Zack Barresse (Jul 25, 2004)

It's unfortunate VBScript can't make use of the Format() function. I use the DatePart() often in lieu of it. But with the Excel application object referenced we can just utilize its properties/methods.

Unless I'm missing something, you could just use...

```
For Each rCell In objSheet.UsedRange
	On Error Resume Next
	If IsDate(rCell.Value) Then
		rCell.NumberFormat = "m/d/yyyy"
	End If					
Next
```
Remember, if you want what the cell is showing in the cell use the Text property. If you want the underlying value, use the Value property. Does this help?


----------



## Squashman (Apr 4, 2003)

Thanks Zack. I guess I was over thinking that one.

I don't think I have any control over the way the Save As works. I believe it saves the files with the formatting which is why I needed to fix the number format.

But maybe you can answer one more question for me. While I was debugging this I added this line of code inside my Loop.

```
wscript.echo objExcel.ActiveCell.Address
```
It continually output the same output: $A$1
I thought it would output the row and column of the cell it was working on.


----------



## Zack Barresse (Jul 25, 2004)

Well it doesn't look like your code activates any other cell, which is fine, activating a cell isn't needed to work with the object(s), so that makes sense.

Re SaveAs, if it's saved in Excel format, no, there's not much you can do, because it retains formatting of individual cells. You could always format the cells as text first, or precede cell data entry with a single apostrophe, which would force that cell to read as text regardless of the format applied.


----------



## Squashman (Apr 4, 2003)

I like the apostrophe idea but there are some cells that the dates are calculated within the worksheet. If it is hard coded in the cell that seems to take care of any formatting but would kill the formula. Better to standardize all the dates to one format. This should work for exporting it to a tab delimited file. Just gave our Mainframe SAS programmer a test file. All the dates look formatted the same. Hope she doesn't have anything else to add to this.


----------



## Zack Barresse (Jul 25, 2004)

You could wrap the date formulas in the TEXT() function? It works like the Format() function does (and is non-existant in VBScript). This would keep it calculated, but specify the format. The end result (Value) would, however, be textual, so dependent calculations may need adjustment(s).

But changing the NumberFormat property of the cell should work as well, so long as it's done _after_ the date entry. Excel likes to change the formatting if it *thinks* it knows what you want, especially with dates.


----------

