iLogic to Change Revision Table Date Format

[Dianna_Harding]

I've searched and found posts  that were close, but not quite. Not sure it's possible after what I've read, but I'm not the most savvy with iLogic.

Ref posts:

Read revision

Change Value

Convert String

What I need is a code similar to the referenced links that will read every row's date and convert it to MM/dd/yy. So if the date is: 7/11/2009, it needs to be converted to 07/11/09 or 7-11-09 -->07/11/09

Any help would be greatly appreciated.

[C_Haines_ENG · Accepted solution]

This should work: 

Sub Main
	
	Dim oDoc As DrawingDocument = ThisDoc.Document
	Dim oSheet As Sheet = oDoc.ActiveSheet
	Dim oRevTable As RevisionTable = oSheet.RevisionTables(1)
	
	For i = 1 To oRevTable.RevisionTableRows.Count
		Dim oRevDate = oRevTable.RevisionTableRows(i).Item("DATE")
		
		Dim oMonth As Integer = Split(oRevDate.Text,"/")(0)
		Dim oDay As Integer = Split(oRevDate.Text,"/")(1)
		Dim oYear As Integer = Split(oRevDate.Text, "/")(2)
		
		oRevDate.Text = oMonth.ToString("D2") & "/" & oDay.ToString("D2") & "/" & Right(oYear, 2)
	
	Next
	
End Sub

[Dianna_Harding]

Thank you! This almost works, but it doesn't like the date formats with "-"  such as 6-19-2025.

System.InvalidCastException: Conversion from string "3-20-13" to type 'Integer' is not valid. ---> System.FormatException: Input string was not in a correct format.
at Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat)
at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)
--- End of inner exception stack trace ---
at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)
at ThisRule.Main() in external rule: _Date Format:line 10
at Autodesk.iLogic.Exec.AppDomExec.ExecRuleInAssembly(Assembly assem)
at iLogic.RuleEvalContainer.ExecRuleEval(String execRule)

[C_Haines_ENG]

How many different formats of dates do you have?

[Dianna_Harding]

I think the two. I'll try a Try--Catch statement to check for the dash and see if that works, but the rest of it is perfect. Thank you!

[C_Haines_ENG · Accepted solution]

We can just compensate for "wildcards" :

Sub Main

	Dim oDoc As DrawingDocument = ThisDoc.Document
	Dim oSheet As Sheet = oDoc.ActiveSheet
	Dim oRevTable As RevisionTable = oSheet.RevisionTables(1)

	'ADD WILDCARD STRINGS TO CONVERT DATE FORMATS
	Dim oWildCards As New List(Of String) : oWildCards.AddRange({"-", "_" })

	For i = 1 To oRevTable.RevisionTableRows.Count
		
		Dim oRevDate As RevisionTableCell = oRevTable.RevisionTableRows(i).Item("DATE")
		Dim oRevDateText As String = oRevDate.Text

		For Each oWildCard As String In oWildCards
			oRevDateText = Replace(oRevDateText, oWildCard, "/")
		Next

		Dim oMonth As Integer = Split(oRevDateText, "/")(0)
		Dim oDay As Integer = Split(oRevDateText, "/")(1)
		Dim oYear As Integer = Split(oRevDateText, "/")(2)

		oRevDate.Text = oMonth.ToString("D2") & "/" & oDay.ToString("D2") & "/" & Right(oYear, 2)

	Next

End Sub

[Dianna_Harding]

Worked perfect, thank you!

[Dianna_Harding]

Stress testing this and noticed that if the code is run more than once, it shortens any year converted to start with a zero to one digit. Is there a way to keep the zero?

[C_Haines_ENG · Accepted solution]

I should have thought about that. Try this:

Sub Main

	Dim oDoc As DrawingDocument = ThisDoc.Document
	Dim oSheet As Sheet = oDoc.ActiveSheet
	Dim oRevTable As RevisionTable = oSheet.RevisionTables(1)

	'ADD WILDCARD STRINGS TO CONVERT DATE FORMATS
	Dim oWildCards As New List(Of String) : oWildCards.AddRange({"-", "_" })

	For i = 1 To oRevTable.RevisionTableRows.Count

		Dim oRevDate As RevisionTableCell = oRevTable.RevisionTableRows(i).Item("DATE")
		Dim oRevDateText As String = oRevDate.Text

		For Each oWildCard As String In oWildCards
			oRevDateText = Replace(oRevDateText, oWildCard, "/")
		Next

		Dim oMonth As Integer = Split(oRevDateText, "/")(0)
		Dim oDay As Integer = Split(oRevDateText, "/")(1)
		Dim oYear As Integer = Right(Split(oRevDateText, "/")(2), 2)

		oRevDate.Text = oMonth.ToString("D2") & "/" & oDay.ToString("D2") & "/" & oYear.ToString("D2")

	Next

End Sub