consolidate dates in horisontal format

OshaK

Hello,

we track our faculty away dates with SS Calendar template where our admins need to check the checkbox for the day a faculty will be way. I need to be able to report those dates in the horizontal and consolidated format because we share it broader and use in many reports.

So for example, if the dates are reported like that:

I need a sheet (ideally offload the excel file) that would look like this in a tabular format where each month is a cell heading, and all the dates in that month are in the single cell below:

January February March

9-11, 15, 19-23 8, 15-20 3, 5, 12, 23-28

Thank you!

Jason Albrecht

Hi @OshaK

My first thought is to either filter or create a report from your sheet, that shows only the checked boxes.

Then export to Excel, copy the column data and then paste special... transpose.

Would this work for you?

OshaK

@Jason Albrecht thank you, we're trying to minimize manual copy/pasting, that was the main goal with SmartSheet. Appreciate your help, thank you.

Jason Albrecht

Hi @OshaK

I'm not sure how much you know about Microsoft's Power Automate, but I understand you can set up this process to happen automatically.

In other words, between Smartsheet and Power Automate there would be minimal human intervention.

A quick web search on the phrase "power automate transpose excel table" produces a range of answers.