Time & Dates Summarized Across Multiple Spreadsheets

Question

I have no idea how to even begin this project so I would appreciate any help I can get.

I have a work book with many spreadsheets, on each spreadsheet I have the following columns | Date | Start Time | Finish Time | Total Time|, and each spreadsheet is for a different project.

On a Summary spreadsheet I would like the grand total (of ALL the spreadsheets) of how much time was spent on any given date across all the projects.

Some notes:

• On any given spreadsheet there are often many rows with the same date they just have different start and finish times.
• The spreadsheets have no rows for dates with no activity so there could be days, weeks, or months between dates.
• The dates on one spreadsheet would most likely be on different rows than any other spread sheet.

Answer 1

Hi,

This data is not in a format for Power Query to work on. Sorry I cannot help with this.

Answer 2

Unfortunately I cannot figure out how to adapt Power Query to my needs.

Sometimes I do not explain myself very well so I'd like to try again.

In C:33 of the Calendar spreadsheet I would like a total of all the hours in column D of the Oakdale spreadsheet for the corresponding date.

I am trying to figure out a formula that automatically searches ALL of column A of the Oakdale spreadsheet for a certain date eg. September 1, 2021 then once it finds the cells with that date in it it grabs the data from the adjacent cell(s) in column D summarizes it and places the obtained data in the appropriate cell C:33 in the Calendar spreadsheet.

Sometimes a date doesn't exist on the Oakdale spreadsheet so the posted value on the Calendar spreadsheet should be '0:00' or '-'

Sometimes there is only one row per date and sometimes there are lots of rows with the same dates so the values in all the rows for the specific date need to be added together before they are posted on the Calendar spreadsheet.

Answer 3

Hi,

That video shows you how to append data from multiple worksheets into a single worksheet. Once you have all data in 1 worksheet, you can write formulas easily. In your version, Power Query is available in Data > Get & Transform.

Answer 4

I tried following the video however I do not have the Power Query tab in my Excel. Also as I followed the video I think it was going in a different direction than I was wanting to go.

I am looking to summarize the total number of hours spent on any given day on all projects combined. So the formula will need to search a column on all spreadsheets for a specific date then obtain the hourly data in one of the neighboring cells for the hourly value for that job and then add it to all like data from all other spreadsheets to obtain a grand total number of hours spent on that specific date.

Answer 5

Hi,

Follow this video - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks.