Forum Discussion

sonya_m's avatar
sonya_m
SmartBear Alumni (Retired)
5 years ago

[TechCorner Challenge #11] Converting UTC TimeDate in an Excel file

Hi everyone!

 

Up for a challenge? By completing the tasks, you can practice your skills of working with TestComplete features and also get into the TechCorner Leaderboard!

 

Today, we are going to work with UTC DateTime stored 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.

 

Difficulty:

 

Note: Find the Excel file attached.

 

Steps to do this:

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.

 

Best of luck to all participants!

Scripting
TechCornerChallenge
  • SiwarSayahi's avatar
    SiwarSayahi
    5 years ago

    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.

     

    This is a solution created for [TechCorner Challenge #11]

     

     

    [Jscript]

     

     

     

       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 );


   }

     

     

  • elanto's avatar
    elanto
    5 years ago

    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.

     

    This is a solution created for [TechCorner Challenge #11]

     

     

    Hi sonya_m and SiwarSayahi,

    I try again.

     

    # 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;

     

  • SiwarSayahi's avatar
    SiwarSayahi
    Occasional Contributor
    5 years ago

    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.

     

    This is a solution created for [TechCorner Challenge #11]

     

     

    [Jscript]

     

     

     

       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 );


   }

     

     

    • sonya_m's avatar
      sonya_m
      SmartBear Alumni (Retired)
      5 years ago

      Thank you for participating everyone!🙂

       

      Hi elanto! Hmmm, looks like the dates posted to the log are not quite correct. You can check out the script by SiwarSayahi and see how this can be done and build the script from there🙂

       

      SiwarSayahi The script works great! However, to match the task criteria fully, it should also convert UTC time to PST time. Would you like to try and improve your script this way?

  • elanto's avatar
    elanto
    Occasional Contributor
    5 years ago

    Hi everyone, Friday aftenoon... a challenging code week just behind... trying to give a quick first attempt.

    I see that something is not working properly... but now the chief is calling again and again and I have really to go... sorry..

    Kind regards

    E.

    ______________

    procedure Challenge_11();
    var fileExcel, exSheet, ValA1, ValA2, ValA3: OleVariant;
    i: Integer;
    begin
       fileExcel := Excel.Open('C:\\Temp\DateTime.xlsx');
       exSheet := fileExcel.SheetByTitle['Sheet1'];

       for i := 1 to exSheet.RowCount do
       begin
            Log.Message(aqConvert.DateTimeToFormatStr(aqDateTime.GetMonth(exSheet.Cell('A', i).Value), '%B') + ' ' +       IntToStr(aqDateTime.GetDay(exSheet.Cell('A', i).Value)) + ' , ' +
            aqConvert.DateTimeToFormatStr(aqDateTime.GetDay(exSheet.Cell('A', i).Value), '%A'));
    end;