2023 1 Securities and Exchange Commission Use date and time functions to complete the tasks 1 1 Use the TODAY

  

1. Securities and Exchange Commission

Use date and time functions to complete the tasks.

1.1

Use the TODAY function to insert the current date in cell C6.

1.2

Use the NOW function to insert the current time in cell C8.

1.3

Use the MONTH function in cell C10 to calculate the month of the year for the date of the formation of the SEC, entered in cell C4.

1.4

Use the YEAR function in cell C12 to calculate the year for the date of the formation of the SEC, entered in cell C4.

1.5

Calculate the difference between the date of the formation of the SEC, entered in cell C4, and the current date in cell C6. Insert this calculation in cell C14.

1.6

Calculate the number of hours since the date of the formation of the SEC. Insert your calculation in cell C15.

Hint: convert the number of days to number of hours by multiplying the number of days in C14 by 24.

1.7

Calculate the number of minutes since the date of the formation of the SEC. Insert your calculation in cell C16. Be sure to reference the number of hours calculated in the previous task in your calculation.

1.8

Calculate the number of seconds since the date of the formation of the SEC. Insert your calculation in cell C17. Be sure to reference the number of minutes calculated in the previous task in your calculation.

2. Security

You are responsible for monitoring employees’ entry into your company’s server room. To complete this task, you need to review a log of when employees entered the room. The log is a little bit difficult to read. It contains:

· An employee number (column B).

· A computer generated stamp that records when the employee swiped their ID card to enter the room (column C).

Ultimately, you want to create the “Text Stamp” in column K that completes the phrase, “Employee number # entered the server room at HH:MM:SS today.” for each row of the log.

You will replace “#” with the employee number and “HH:MM:SS” with the appropriate time stamp.

Use time and text functions to complete the tasks below.

2.1

Use the LEN function in cell C3 to calculate the length of the “Text Stamp Phrase” in cell C2.

2.2

Use the SEARCH function in cell C4 to determine the position of the “#” symbol in the “Text Stamp Phrase” in cell C2.

2.3

Use the LEFT function in cell C5 to return the text “Employee number ” from the “Text Stamp Phrase” in cell C2. Notice the space after number.

a. Use a reference to the location of the “#” symbol in cell C4 as the “[num_chars]” argument.

b. Since the “#” symbol is one 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 C4.

2.4

Use the SEARCH function in cell C6 to determine the position of the characters ” HH” (notice the space before the first H) in the “Text Stamp Phrase” in cell C2.

2.5

Use the MID function in cell C7 to return the text ” entered the server room at ” (notice the spaces at the beginning and end of the phrase) from the “Text Stamp Phrase” in cell C2.

a. Use a reference to the location of the “#” symbol in cell C4 as the “start_num” argument.

b. 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 C4.

c. Use the difference between the location of the “#” symbol (cell C4) and the characters ” HH” (cell C6) as the “num_chars” argument.

2.6

Use the RIGHT function in cell C8 to return the text ” today.” from the end of the “Text Stamp Phrase” in cell C2.

The “[num_chars]” argument for your function is 7 since there are seven characters in the text ” today.” (including the space at the beginning).

2.7

Use the HOUR function in cell D12 to calculate the “Hour” portion of the “Entry Swipe” found in cell C12. Copy and paste the function down to complete the “Hour” column of the table.

2.8

Use the MINUTE function in cell E12 to calculate the “Minute” portion of the “Entry Swipe” found in cell C12. Copy and paste the function down to complete the “Minute” column of the table.

2.9

Use the SECOND function in cell F12 to calculate the “Second” portion of the “Entry Swipe” found in cell C12. Copy and paste the function down to complete the “Second” column of the table.

2.10

Use the CONCAT function in cell J12 to combine the text in cells G12, H12, and I12 to create a “Time Stamp”.

a. Notice that the syntax for the “Time Stamp” is “HH:MM:SS”.

b. You will need to insert the “:” symbol between “Hour” and “Minute” and between “Minute” and “Second”.

c. Hint: you should have five arguments for your function. arguments 2 and 4 should be the “:” symbol.

d. Copy and paste your function to complete the “Time Stamp” column of the table.