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!

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

     

     

  • 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

    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)

      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

        Hi,

        thanks sonya_m and SiwarSayahi .

        I'll hopefully find this week a quite moment where I can fix it. therefore the answer is yes, I am going to improve my script.

        I wish you a nice working day,

        E.

  • elanto's avatar
    elanto
    Occasional Contributor

    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;