Working with date columns using Copilot in Excel

[EricPatterson]

Greetings, This is the third in a series of posts that provides examples of what is possible with Copilot in Excel.  Today I will continue with the list of employees that now contains a column for the hire date.

Table with these columns: Name Address City State. First two rows of data are: Claude Paulet 123 Main Avenue Bellevue Washington 8/24/2022 Jatindra Sanyal 1122 First Place Ln N Corona California 8/14/2023

I would like to add columns for which quarter and year the employees were hired to this table. To accomplish this, I'll start by clicking on the copilot button on the right side of the Home tab, showing the copilot pane and type the prompt:

 add columns for the quarter and year of the hire date

Copilot in Excel looks at the content in the table and then suggests inserting 2 calculated columns, one for the hire quarter and one for the hire year.

Looking at A1:E17, here are 2 formula columns to review and insert in Columns F and G: 1. Hire Quarter Determines the quarter in which each employee was hired by converting the hire date into a corresponding quarter (Q1, Q2, Q3, or Q4) based on the month of the year. =CHOOSE(CEILING(MONTH([@[Hire Date]])/3,1),"Q1","Q2","Q3","Q4") Show explanation 2. Hire Year Extracts the year from each hire date, providing a quick way to see when each person was hired. =YEAR([@[Hire Date]])

Hovering the mouse cursor over the "Insert columns" button in the copilot pane shows a preview of what inserting the new column formulas will look like.  From the preview, it looks like it is doing what I wanted.

Picture of the list of employees with a preview of the new hire quarter and hire year columns that would be added.

Clicking on the Insert Columns button will accept the proposed change, inserting 2 new calculated column formulas that use the hire date column to determine the quarter and year of each hire. This is exactly the result I was looking for!

Picture showing the Excel workbook with copilot pane open. Includes the employee table with hire quarter and hire year added.

Over the coming weeks I will be sharing more examples of what you can do with Copilot in Excel.

Thanks for reading,

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.