Convert DateTime Util from US Format (MM/dd/yyy) to UK Format (dd/MM/yyyy)
All,
I wish to convert the default US Format into UK Format as part of an automation.
The automation I have is very simple see screen shot:
However I am presented with the error below:
The script I am trying to use to convert the format from MM/dd/yyyy to dd/MM/yyyy is below:
All,
I wish to convert the default US Format into UK Format as part of an automation.
The automation I have is very simple see screen shot:
However I am presented with the error below:
The script I am trying to use to convert the format from MM/dd/yyyy to dd/MM/yyyy is below:
public string Dateformat(string USdate)
{
string date = USdate; // en-US formatted date
// Convert to DateTime
DateTime parsedDate = DateTime.ParseExact(date, "MM/dd/yyyy", CultureInfo.InvariantCulture);
// Output in en-GB format
return parsedDate.ToString("dd/MM/yyyy");
}
Any help to resolve this would be much appreciated. The desired result is when I transfer Now() into an excel spreadsheet the format of the date is dd/MM/yyyy
Pegasystems Inc.
US
Hi,
No need to use script here. Date and time are stored as System.DateTime underneath, so you can extract proxy and then use its ToString(String format) method:
Nationwide Building Society
GB
Thanks for the prompt response, the issue seems to occur when I am setting a cell value in excel. Please see screen shot below:
Message Dialog = 06/11/2018 (Correct) dd/MM/yyyy
Cell Value E1 = 11/06/2018 (Incorrect) MM/dd/yyyy I have tried formatting the cells to short date & general in excel.
Pegasystems Inc.
US
Excel converts stored date value automatically, so make sure "Locale (location)" value for Date format in Excel is a correct one. I think you need "English (United Kingdom)":
Pegasystems Inc.
US
Actually this is Excel who changes format to what it thinks is correct. Let's try to force column formatting with the script below:
public void FormatDate(Worksheet ThisSheet, String cell1)
{
Range rg = ThisSheet.get_Range(cell1, System.Type.Missing);
rg.NumberFormat = "DD/MM/YYYY";
Marshal.FinalReleaseComObject(rg);
}
Then change automation as shown below. Use OADate instead of String to make sure Excel doesn't make incorrect assumptions about entered value. "A:A" selects entire column A.
I have attached my solution for reference. Change path in microsiftExcel1.Open block to your excel file if you want to run it.
Nationwide Building Society
GB
Works perfectly, thank you for looking into this issue its of great help to us!
Quick question how did you kick off the automation without a use of button?
