Hi everyone, over the last few weeks we have had a series of posts to show you some of the things that are possible to do with Copilot in Excel.

This week I have a task that I need to get done quickly. I need to take some meeting attendance data that I have from our recent business partner meetings and do some analysis on it. Before I can do the analysis, I need to clean up the data a bit.

Table of partner meetings with columns for Meeting name, Attendee ID, Join Time and Leave Time

To start, I see that the Join and Leave Time columns aren’t actual dates, they are just entered as text. I ask Copilot:

add a calculated column formula that converts the Join Time column to a date/time value

Copilot in Excel pane with the above prompt and the response below

Looking at B2:E137, here’s 1 formula column to review and insert in Column F:

Converts the join time of each attendee into a numerical value by extracting the date and time components from the “Join Time” column. This helps in performing time-based calculations and comparisons within the table.

=DATEVALUE(LEFT([@[Join Time]],FIND(“,”,[@[Join Time]])-1))+TIMEVALUE(MID([@[Join Time]],FIND(“,”,[@[Join Time]])+2,LEN([@[Join Time]])))

I insert that formula, which works and so I ask it to repeat the same for the Leave time column:

Now use the same type of formula to add a column for Leave time

Copilot in Excel pane with the above prompt and the response below

Looking at B2:F137, here’s 1 formula column to review and insert in Column G:

Converts the leave time of each attendee into a numerical date-time value, making it easier to perform time-based calculations and comparisons.

=DATEVALUE(LEFT([@[Leave Time]],FIND(“,”,[@[Leave Time]])-1))+TIMEVALUE(MID([@[Leave Time]],FIND(“,”,[@[Leave Time]])+2,LEN([@[Leave Time]])))

After adding the 2 new columns, my table looks like this:

Table of partner meetings with columns for Meeting name, Attendee ID, Join Time, Leave Time, Join Time Value and Leave Time Value

I rename the columns and hide the original join and leave time columns and the table now looks like this:

Table of partner meetings with columns for Meeting name, Attendee ID, Join Time, and Leave Time.

Next, I want to replace the attendee IDs with attendee names. In the same workbook, I also have this table of Attendee IDs and Names:

Table of attendees with columns for ID and Name

I ask Copilot:

Insert a Name column to the left of Attendee ID by looking up the name in the other table in this sheet

Copilot in Excel pane with the above prompt and the response below

Looking at B2:G137, here’s 1 formula column to review and insert in Column C:

Retrieves the name of each meeting attendee by matching their ID with the corresponding name in the partner meetings table (Table219).

=XLOOKUP([@[Attendee ID]],Table219[ID],Table219[Name])

Copilot suggest a XLOOKUP formula that after inserted makes my table look like this:

Table of partner meetings with columns for Meeting name, Attendee Name, Attendee ID, Join Time, and Leave Time.

Finally, I want to add a column that calculates the duration that each attendee participated in each meeting using the Join and Leave times. I ask Copilot:

Add a column that shows the duration using the join and leave time

Copilot in Excel pane with the above prompt and the response below

Looking at B2:H137, here’s 1 formula column to review and insert in Column I:

Calculates the duration of each attendee’s participation in a meeting by subtracting their join time from their leave time.

=[@[Leave Time]]-[@[Join Time]]

After inserting the suggested formula, my table looks like this, all ready for analysis:

Table of partner meetings with columns for Meeting name, Attendee Name, Attendee ID, Join Time, Leave Time and meeting duration.

Stay tuned,

Microsoft Excel Team

*Disclaimer: If you try these types of prompts and they do not work as expected, it is most likely due to our gradual feature rollout process. Please try again in a few weeks.

​Microsoft Tech Community – Latest Blogs –Read More