Converting UTC TimeDate in an Excel file
Task
Read the UTC DateTime in an Excel file (attached), convert the value for the PST (Pacific Standard Time) time zone and log each date in the following format: <month name> <day of month>, <full weekday name>. For example: September 8, Tuesday.
Steps
- Read the dates from the Excel file using one of the approaches described in Working with Microsoft Excel Files.
- Convert the dates using the aqDateTime object methods.
- Log the date using the aqConvert.DateTimeToFormatStr method.
Solution
Solutions were given within the the TechCornerChallenge event by different community members.
by SiwarSayahi
//JavaScript
function DateFormat()
{
// Creates a driver
DDT.ExcelDriver("C:\\Challenge11\\DateTime.xls", "Sheet1");
// Iterates through records
while (! DDT.CurrentDriver.EOF())
{
//Display the date in the format <month name> <day of month>, <full weekday name>
DisplayDate();
DDT.CurrentDriver.Next();
}
// Closes the driver
DDT.CloseDriver(DDT.CurrentDriver.Name);
}
function DisplayDate()
{
for(i = 0; i < DDT.CurrentDriver.ColumnCount; i++)
var dateA =aqConvert.VarToStr(DDT.CurrentDriver.Value(i));
//Convert the date from UTC to PST
var dateB = aqDateTime.AddHours(dateA, -8);
var date = aqConvert.DateTimeToFormatStr(dateB,"%B %d, %A");
Log.Message( "The date of " + dateA + " is : " + date );
}
by elanto
# DelphiScript
procedure Challenge_11();
var
fileExcel, exSheet, Valx: OleVariant;
i: Integer;
begin
fileExcel := Excel.Open('C:\\Temp\DateTime.xlsx');
exSheet := fileExcel.SheetByTitle['Sheet1'];
for i := 1 to exSheet.RowCount do
begin
Valx := aqDateTime.AddHours(exSheet.Cell('A', i).Value, -8);
Log.Message(aqConvert.DateTimeToFormatStr(Valx, '%B %d, %A'));
end;
end;Published 4 years ago
Version 1.0