Knowledge Base Article

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

  1. Read the dates from the Excel file using one of the approaches described in Working with Microsoft Excel Files.
  2. Convert the dates using the aqDateTime object methods.
  3. 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 3 years ago
Version 1.0

Was this article helpful?

No CommentsBe the first to comment