cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted
Community Manager

[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:Star_Gold.pngStar_gray.pngStar_gray.png

 

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!


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Tags (1)
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Occasional Contributor

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.

 

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


   }

 

 

View solution in original post

Tags (1)
Highlighted
Occasional Contributor

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.

 

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;

 

View solution in original post

Tags (1)
8 REPLIES 8
Highlighted
Occasional Contributor

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

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;

Highlighted
Occasional Contributor

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.

 

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


   }

 

 

View solution in original post

Tags (1)
Highlighted
Community Manager

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

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?


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Highlighted
Occasional Contributor

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

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.

Highlighted
Occasional Contributor

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.

 

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;

 

View solution in original post

Tags (1)
Highlighted
Occasional Contributor

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

@sonya_m  thank you for your reply, I had edited my solution.

Highlighted
Community Manager

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

@SiwarSayahi , @elanto the scripts you created look awesome! Great job!🏆


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Highlighted
Community Manager

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

a short UPD

 

@SiwarSayahi Looks like you made it to the top of the Leaderboard!

Congrats!🎆

 

@elanto , congratulations on the first TechCorner badge in your collection💪

Your manager should be proud and not interfere with your participation in the TechCorner Challenge anymore🙂


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors