consolidate dates in horisontal format

Options

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!

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    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?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • OshaK
    OshaK ✭✭✭✭
    Options

    @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
    Jason Albrecht ✭✭✭✭✭✭
    Options

    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.

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!