2

Our team runs many short, streamlined projects. We currently have:

  • A master excel sheet containing current phase/key data of each project
  • Personal .xls tracking tools which automatically calculate a timeline for each project
  • Templated docsets for each project, also containing some of this data as metadata (for views)

As project progresses, we are updating each of these three systems individually, which inevitably leads to consistency issues. It would be great if the three were linked. I looked at replacing the master tool with a sharepoint list, but I’m running into one main issue around date calculation. I need a calculated column that:

  • Takes into account weekdays
  • Takes into account public holidays
  • Has the ability to be overwritten manually.

Is this something I am ever going to be able to accomplish using calculated columns? Or should I look into flows? Or would it be better to keep this master excel sheet, and focus on finding a flow that makes this data flow though to other systems? I’m hoping for your guidance as our IT department apparently has no idea how to accomplish this and generally lacks the know-how. I’m willing to learn, but need some guidance :) Thanks!

1

Assuming you could get the logic correct for the calculation, a calculated column couldn't also be set manually. As an alternative, you could have four columns:

  • calculated column
  • date column (for manual entry)
  • manual/calculated flag field (yes/no or choice field that allows the user to set if the row should use the value in the calculated field or the manual field
  • result field: the value of either the calculated field or the manual field, depending on the value of the flag

A Flow could easily populate the result field, based on the flag. If the calculation is too difficult to do in a sharepoint calculated field, you could always do the calculation in a flow or in an azure function. (a calculated field would probably work, but it's going to be ugly)

  • Thank you for your suggestion. Based on the amount of calculated columns I need (at least 10), multiplying these by 4 would be terrible. I believe a workflow can be made to accomplish this, but I'm not too sure if I'll be overcomplicating things. Maybe Excel is the way to go for now. – Robin Verstappen Jan 23 at 2:35

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.