Dis-aggregate SS start and end dates in exported Excel

Gordon

Hello,

 

I am trying to see if any of the Excel wizards here can help me with a script or other macro that will disaggregate my SS exported project sheet data that contains start and end date columns (along with the usual duration column) information into individual rows for each day/date that is bounded by the SS start and end dates.

 

Reason is because I need to compare scheduled task time data (in SS) to actual time reported in another system.  Due to the lack of cross-sheet VLOOKUP capability, I have discovered Power BI and am considering building a system where I combine Power BI with Excel data that I export from SS.  Unfortunately, Power BI doesn't handle date range data very well (i.e., at all!) and can only visualize/analyze date-based data based on a single date column.  Therefore I need to disaggregate each of my project sheet's tasks in SS into multiple rows in excel (one per date), which I can then import into Power BI for further analysis.

 

Thanks!

Shaine Greenwood

Hi Gordon,

 

If you don't get an answer to your questions here, you might check to see if a Smartsheet trusted partner can assist you with this: https://www.smartsheet.com/partners/partner-directory