Can I automate splitting a range of dates into separate rows

JBragg

Hi, I'm working on a PTO calendar for my department. It's impractical to ask my team to fill out a separate form for each day they are requesting off... is there a way I can have them enter a range of Dates, PTO Start Date and PTO End Date, and have that split into separate rows on my grid? There are different duties that need to have coverage assigned based on the employee and the day(s) of the week they are out. Thanks!

reece.buckner

could you explain further how the PTO calendar interacts with the duties that need to be covered? I assume that they are different sheets? Maybe a screenshot of both sheets.

JBragg

This is the form I'm using at the moment. I'd like to connect a spreadsheet that has their daily duties listed to this so they have to list out who is covering each individual task. For instance, if Jane Doe is out 10/31-11/2, her tasks are listed out for Mon, Tues, and Wed to ensure everything is covered. I'm pretty sure I can do it by linking sheets if each date is on an individual line, but can Smartsheet convert a range of dates from a form to separate lines? For now, they can put their coverage contacts into one column but they have to list out what's being covered manually. I hope this makes sense.

Genevieve P.

Hi @JBragg

Can you share a screen capture of the second sheet, where you have the dates parsed out?

Do you have one column for the dates, then one column per-person?

There currently isn't a way for Smartsheet to automatically create the correct number of rows based on the working dates between to dates in a Form submission. However you could use a formula in second sheet to check a box if the date in the current row is within a date range in your intake sheet!

I'm imagining a secondary sheet like this:

=COUNTIFS({Start Date}, <=Date@row, {End Date}, >=Date@row, {Contact Column}, "Genevieve")

Would that help?

There could be other formulas we could do in a second sheet, depending on how many people you have. For example, you could have one PTO sheet per person with all dates listed down one column, like above. Then you could use a formula that checks to see if a current date is within the date range (same as above) and if it is, bring back cell content from your original sheet instead of a checkbox.

This way instead of a checkbox you're bringing in duplicate data for each row (e.g the Coverage Contacts). Then you could use a Report to bring all of your sheets into one place and filter out the blank rows.

Cheers!

Genevieve