consolidate dates in horisontal format

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!

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.