Howard has an account with a major shipping company to deliver all of their orders. This shipping company sends him a log of all shipments that were delivered each day. The log is a little bit difficult to read. It contains a shipment number (column B of the table below) as well as a computer generated time stamp (column Cof the table below) that corresponds with the time of the day that each shipment was delivered. Use time and text functions to complete the tasks below. Ultimately you want to create the "Text Stamp" in column K that completes the phrase, "Shipment number # was delivered at HH:MM:55 today." for each row of the log. You will replace "#" with the employee number and "HH:MM:SS" with the appropriate time stamp. Text Stamp Phrase: Shipment number # was delivered at HH:MM:SS today. Phrase length: Location of "# Hint: Tasks 1-6 will manipulate the "Text Stamp Phrase" in Phrase Part 1 cell C7 so that the completed phrase can be entered in the Location of " HH" table below. Phrase Part 2 Phrase Part 3 Hour Part Minute Part Second Part Text Stamp Shipment 1039 1041 1042 1045 1046 1049 1052 1054 1055 1056 1058 1059 1061 1062 1064 100 Shipper's Time Stamp 0.03717 0.07747 0.07966 0.11619 0.12878 0.13432 0.15067 0.15813 0.15922 0.16538 0.17819 0.19567 0.20784 0.22641 0.2408 11 2 Decimal Equivalents Hours Minutes Seconds Time Stamp 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Task # Points Task Description 1 5 Use the LEN function in cell C8 to calculate the length of the "Text Stamp Phrase" in cell C7. 2 5 Use the SEARCH function in cell C9 to determine the position of the "#" symbol in the "Text Stamp Phrase" in cell C7. 3 6 Use the LEFT function in cell C10 to return the text "Shipment number" (notice the space after number) from the "Text Stamp Phrase" in cell C7. Use a reference to the location of the "#" symbol in cell C9 as the "[num_chars]" argument. Since the "#" symbol is 1 character past the text you want to return, you will need to adjust the "[num_chars)" argument by subtracting 1 from the reference to cell 09. 4 6 Use the SEARCH function in cell C11 to determine the position of the characters " HH" (notice the space before the first H) in the "Text Stamp Phrase" in cell c7. 5 5 Use the MID function in cell C12 to return the text " was delivered at " (notice the spaces at the beginning and end of the phrase) from the "Text Stamp Phrase" in cell C7. Use a reference to the location of the "#" symbol in cell C9 as the "start_num" argument. Since the "#" symbol is 1 character before the text you want to return, you will need to adjust the "[num_chars)" argument by adding 1 to the reference to cell 09. Use the difference between the location of the "#" symbol and the characters " HH" as the "num_chars" argument. 6 6 Use the RIGHT function in cell C13 to return the text "today." from the end of the "Text Stamp Phrase" in cell C7. The "[num_chars)" argument for your function is 7 since there are 7 characters in the text " right." (including the space at the beginning). 7 5 Use the HOUR function in cell D17 to calculate the "Hour" portion of the "Shipper's Time Stamp" found in cell C17. Copy and paste the function down to complete the "Hour Part" column of the table. 8 5 Use the MINUTE function in cell E17 to calculate the "Minute" portion of the "Shipper's Time Stamp" found in cell C17. Copy and paste the function down to complete the "Minute Part" column of the table. 9 5 Use the SECOND function in cell F17 to calculate the "Second" portion of the "Shipper's Time Stamp" found in cell C17. Copy and paste the function down to complete the "Second Part" column of the table. 10 6 Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013 or earlier) in cell J17 to combine the text in cells G17, H17, and 117 to create a "Time Stamp". Notice that the syntax for the "Time Stamp" is "HH:MM:SS". You will need to insert the ":" symbol between "Hour" and "Minute" and between "Minute" and "Second". (Hint: you should have five arguments for your function, arguments 2 and 4 should be the ":" symbol.) Copy and paste your function to complete the "Time Stamp" column of the table. 11 6 Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013 or earlier) to combine the text in cells C10, B17, C12, J17, and C13 (in that order) to create the "Text Stamp" in cell K17. The "Text Stamp" in cell K17 should read "Shipment number 1039 was delivered at 00:53:31 today." Copy and paste your function to complete the "Text Stamp" column of the table. Total: 60 Howard has an account with a major shipping company to deliver all of their orders. This shipping company sends him a log of all shipments that were delivered each day. The log is a little bit difficult to read. It contains a shipment number (column B of the table below) as well as a computer generated time stamp (column Cof the table below) that corresponds with the time of the day that each shipment was delivered. Use time and text functions to complete the tasks below. Ultimately you want to create the "Text Stamp" in column K that completes the phrase, "Shipment number # was delivered at HH:MM:55 today." for each row of the log. You will replace "#" with the employee number and "HH:MM:SS" with the appropriate time stamp. Text Stamp Phrase: Shipment number # was delivered at HH:MM:SS today. Phrase length: Location of "# Hint: Tasks 1-6 will manipulate the "Text Stamp Phrase" in Phrase Part 1 cell C7 so that the completed phrase can be entered in the Location of " HH" table below. Phrase Part 2 Phrase Part 3 Hour Part Minute Part Second Part Text Stamp Shipment 1039 1041 1042 1045 1046 1049 1052 1054 1055 1056 1058 1059 1061 1062 1064 100 Shipper's Time Stamp 0.03717 0.07747 0.07966 0.11619 0.12878 0.13432 0.15067 0.15813 0.15922 0.16538 0.17819 0.19567 0.20784 0.22641 0.2408 11 2 Decimal Equivalents Hours Minutes Seconds Time Stamp 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Task # Points Task Description 1 5 Use the LEN function in cell C8 to calculate the length of the "Text Stamp Phrase" in cell C7. 2 5 Use the SEARCH function in cell C9 to determine the position of the "#" symbol in the "Text Stamp Phrase" in cell C7. 3 6 Use the LEFT function in cell C10 to return the text "Shipment number" (notice the space after number) from the "Text Stamp Phrase" in cell C7. Use a reference to the location of the "#" symbol in cell C9 as the "[num_chars]" argument. Since the "#" symbol is 1 character past the text you want to return, you will need to adjust the "[num_chars)" argument by subtracting 1 from the reference to cell 09. 4 6 Use the SEARCH function in cell C11 to determine the position of the characters " HH" (notice the space before the first H) in the "Text Stamp Phrase" in cell c7. 5 5 Use the MID function in cell C12 to return the text " was delivered at " (notice the spaces at the beginning and end of the phrase) from the "Text Stamp Phrase" in cell C7. Use a reference to the location of the "#" symbol in cell C9 as the "start_num" argument. Since the "#" symbol is 1 character before the text you want to return, you will need to adjust the "[num_chars)" argument by adding 1 to the reference to cell 09. Use the difference between the location of the "#" symbol and the characters " HH" as the "num_chars" argument. 6 6 Use the RIGHT function in cell C13 to return the text "today." from the end of the "Text Stamp Phrase" in cell C7. The "[num_chars)" argument for your function is 7 since there are 7 characters in the text " right." (including the space at the beginning). 7 5 Use the HOUR function in cell D17 to calculate the "Hour" portion of the "Shipper's Time Stamp" found in cell C17. Copy and paste the function down to complete the "Hour Part" column of the table. 8 5 Use the MINUTE function in cell E17 to calculate the "Minute" portion of the "Shipper's Time Stamp" found in cell C17. Copy and paste the function down to complete the "Minute Part" column of the table. 9 5 Use the SECOND function in cell F17 to calculate the "Second" portion of the "Shipper's Time Stamp" found in cell C17. Copy and paste the function down to complete the "Second Part" column of the table. 10 6 Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013 or earlier) in cell J17 to combine the text in cells G17, H17, and 117 to create a "Time Stamp". Notice that the syntax for the "Time Stamp" is "HH:MM:SS". You will need to insert the ":" symbol between "Hour" and "Minute" and between "Minute" and "Second". (Hint: you should have five arguments for your function, arguments 2 and 4 should be the ":" symbol.) Copy and paste your function to complete the "Time Stamp" column of the table. 11 6 Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013 or earlier) to combine the text in cells C10, B17, C12, J17, and C13 (in that order) to create the "Text Stamp" in cell K17. The "Text Stamp" in cell K17 should read "Shipment number 1039 was delivered at 00:53:31 today." Copy and paste your function to complete the "Text Stamp" column of the table. Total: 60