Convert the date in ISO format

[Prachijain]

Hi,
I'm struggling to convert the date into ISO format which is giving me wrong results while I compare two different sheet.

My inputs are
[TABLE="width: 3770"]
<tbody>[TR]
[TD="class: xl63, width: 110, align: right"]4/9/1984[/TD]
[TD="class: xl63, width: 106, align: right"]8/1/2007[/TD]
[TD="class: xl64, width: 97"]F[/TD]
[TD="class: xl64, width: 162, align: right"]21006.87[/TD]
[TD="class: xl64, width: 151"]Regular School District[/TD]
[TD="class: xl64, width: 166, align: right"]1[/TD]
[TD="class: xl64, width: 179"]Average[/TD]
[TD="class: xl64, width: 199, align: right"]34[/TD]
[TD="class: xl64, width: 206, align: right"]54[/TD]
[TD="class: xl64, width: 87, align: right"]57[/TD]
[TD="class: xl64, width: 158, align: right"]57[/TD]
[TD="class: xl64, width: 165, align: right"]57[/TD]
[TD="class: xl64, width: 177, align: right"]1[/TD]
[TD="class: xl64, width: 182, align: right"]4[/TD]
[TD="class: xl64, width: 194, align: right"]33.4[/TD]
[TD="class: xl64, width: 214, align: right"]18245.93[/TD]
[TD="class: xl64, width: 214, align: right"]259616.66[/TD]
[TD="class: xl66, width: 158"]18747.37[/TD]
[TD="class: xl63, width: 107, align: right"]1/1/2018[/TD]
[TD="class: xl64, width: 140, align: right"]22508.86[/TD]
[TD="class: xl64, width: 137, align: right"]0[/TD]
[TD="class: xl63, width: 104, align: right"]1/1/1900[/TD]
[TD="class: xl64, width: 199, align: right"]52075.99[/TD]
[TD="class: xl65, width: 158, align: right"]18747.37[/TD]
[/TR]
</tbody>[/TABLE]

In my inputs the four cells consists of date where they need to pass the web service call in ISO format. As my api as a ISO format date.

Public Sub exceljson2(input1, rowloop)Dim http As Object, JSON As Object, arr As String
Set http = CreateObject("MSXML2.XMLHTTP")




http.Open "POST", "https://bitlb02.poolt.hewitt.com/dsi0042/calculator", False
http.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
http.send input1
'MsgBox (http.responseText)




arr = http.responseText
arr = Replace(arr, "{", "")
arr = Replace(arr, "}", "")
Dim LArray() As String
Dim RArray() As String


LArray() = Split(arr, ",")


ub = UBound(LArray())
Dim myString As String


For a = 0 To ub
RArray() = Split(LArray(a), ":")
myString = myString + RArray(0) + ": " + RArray(1) + ","
DQ = Chr(34)
Worksheets("Sheet1").Cells(1, a + 1).Value = Replace(RArray(0), DQ, "") ' title row
Worksheets("Sheet1").Cells(rowloop, a + 1).Value = Replace(RArray(1), DQ, "")
Next a


End Sub

This is the VBA code. Any help is greatly appreciated . Please help

 

[shg]

Format(someDate, "yyyy-mm-dd") returns a string with the specified date in ISO 8601 format.

 

[Prachijain]

Hey Shg, I'm new on this Vba programming. can you please tell me how to integrate the same in my existing program. I don't want the changed format date in excel sheet to be displayed , I want the it to get converted in that form before the api call.

 

[shg]

I don't know where the date is supposed to appear. Your sub doesn't read any cell values.

 

[Prachijain]

Below is my Sub program.

Sub run()
'
' run Macro
' y = Application.Evaluate(Names("Bill").Value)


Dim c As Range
Dim w As Integer
Dim h As Integer
Dim rowloop As Integer
Dim colloop As Integer






Windows("validator.xlsm").Activate


w = Range("testcases").Columns.Count
h = Range("testcases").Rows.Count
o = Range("outputs").Columns.Count


Sheets("op").Select
Range("A2").Select ' this decides the initial cell into which we will paste the first copy/paste operation in outputs


'Call YK_Start and YK_End are used only to improve performance
Call YK_Start


For rowloop = 2 To h 'iterate over each of the three rows of inputs
JSON = ""
For colloop = 1 To w ' first fill in all the inputs to the spreadsheet
Call PasteInput(Range("testcases")(colloop)(1), Range("testcases")(colloop)(rowloop))
JSON = JSON + "'" + Range("testcases")(colloop)(1) + "':'" + CStr(Range("testcases")(colloop)(rowloop)) + "',"
Next colloop
JSON = "{" + Left(JSON, Len(JSON) - 1) + "}"
JSON = "Site=SBA_Modeler_V31&Data=" + JSON
'MsgBox (JSON)
' Calculate is only needed, because YK_Start turns off autocalculate
Calculate
Call GetOutput(o)

Call exceljson2(JSON, rowloop)

Next rowloop


Call YK_End


End Sub

Sub PasteInput(input_name As Variant, v As Variant)


Windows("SBA_Modeler_V31.xlsx").Activate
Range(input_name).Value = v
Windows("validator.xlsm").Activate


End Sub


Sub GetOutput(o As Variant)
For colloop = 1 To o
'MsgBox (Range("outputs")(colloop)(1))
Windows("SBA_Modeler_V31.xlsx").Activate
Range(Range("validator.xlsm!outputs")(colloop)(1)).Select 'this returns a named range from outputs to use as a named range for SBA_Modeler
Selection.Copy ' this will copy the output cell
Windows("validator.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Move selection one to the right
ActiveCell.Offset(0, 1).Select ' this decides the cell into which we will paste the next copy/paste operation
Next colloop
' Move selection down and back
ActiveCell.Offset(1, o * -1).Select
End Sub



I have a program where it converts my date automatically.see below

Sub run()


Dim o As Integer
Dim rowloop As Integer
Dim field As Variant
Dim datereplace As String


o = Range("dates").Rows.Count




For rowloop = 1 To o
field = Range("dates")(1)(rowloop)
If (IsDate(field)) Then
datereplace = Str(Year(field)) + "/" + Trim(Str(Month(field))) + "/" + Trim(Str(Day(field)))
MsgBox ("Date " + Str(field) + " changed to " + datereplace)
Else
MsgBox (Str(field) + " is not a date.")
End If
Next


End Sub


Can you tell me how can I integrate this in my sub.(I don't need the MsgBox).Please help

 

[shg]

Change

datereplace = Str(Year(field)) + "/" + Trim(Str(Month(field))) + "/" + Trim(Str(Day(field)))

to

datereplace = Format(field, "yyyy-mm-dd")

 

[Prachijain]

After I use your line of code my excel application gets freezed and hangs.Basically my excl application crashes and forces to restart the file.do you have any solution for this!!!

 

[shg]

What happens when you step through the code?

http://www.cpearson.com/excel/DebuggingVBA.aspx