Tracking duration of markets engagement

Alicja Sybilska

Hello,

I am working on a tracker for my team. Each member is responsible for managing tasks for a different area for different markets. We want to track how much time each market has spent for each task per months/quarter/year for each area managed by our team. It would be perfect to pull report from the main sheet but I need to enable multiple values per cell in the "Market" column to keep it simple for my team to update.

I thought of creating summaries, e.g. Area 1 - January -- Time spent. But that doesn't seem productive. Any ideas which template would be best and how to workaround inability to create reports for multiple values columns? I am currently using a template for Volunteer Event Calendar

Brian_Richardson

One other thought, if you have the Pivot app it can take a date field and pivot it as Year-Month. So, you could pivot your hours data by Year-Month on the Start and on the Finish and then use that to assist your formulas.

Andrée Starå

Hi @Alicja Sybilska

I hope you're well and safe!

I'd recommend having a separate sheet for either all projects or by project, and then they register the time on that. It could be using a form.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Alicja Sybilska

Hi Andrée,

Thank you for the prompt response, not sure if it would work.

I need calculations on "Hours needed". We have more than 200 markets (multiple values column) for about 5 different areas. The tasks are linked to 1 area but multiple markets. It would be great to calculate how much time was spent for a market in e.g. June for all tasks in a given area, it that makes sense.

I thought of summaries but that would include manual work of 1000 fomulas?

Brian_Richardson

If you're picking multiple markets for a single row, how do you know how many hours were spent in each market on that row?

Ultimately, multiple-selection picklists make this kind of data analysis very difficult, as you're noting. There's simply not a good way to automatically "break up" a multiple-selection array using a formula function without creating a metric sheet that does a HAS formula for each market on a separate row.

There's also not a straightforward way to translate a start/end to months without writing a bunch of formulas for that too. If you have the Resource Management tool, you can sync this type of data to that tool and it will do those calculations for you (and much more). If you're doing it yourself then I'm afraid you have the manual effort of building formulas to watch for time spans in months

You might consider changing how you capture the hours data to have people define the week or month they are submitting for, and save yourself a lot of headache on the hours calculations.

Brian_Richardson

One other thought, if you have the Pivot app it can take a date field and pivot it as Year-Month. So, you could pivot your hours data by Year-Month on the Start and on the Finish and then use that to assist your formulas.

Alicja Sybilska

@Brian_Richardson this is an interesting suggestion! I didn't know about that option, thank you so much for the idea, I'll dive in and see how it can be leveraged