Forum Discussion
Filling in a Table with Intermittent Dates
That is correct. The full monthly calendar in Oak Hill Data would be populated according to the incomplete dates in the data sheets, hopefully.
I see, thank you. Format/structure of the source files is exactly the same as in sheets, or you modified them somehow?
I don't know how to check the minor going ons behind the scenes in Excel, but the format of both dates fields should be mm/dd/yy hh:mm, sampled every 15 minutes.
I mean more data structure
- is each source file have only two column?
- is datetime always in first column and data in second or they could be shifted?
- are columns headers same for each file or they could be different?
- If different for sources when are they they same for monthly files for the same source?
- is there any naming convention for file names?
- are time intervals the same for all sources (starts from 00:00 with every 15 minutes if not skipped, or that could 00:10, 00:25 as well, etc.)
- is each source file have only two column?
No, the source files will sometimes have 2 or 3 columns, the first will however always be date and time
- is datetime always in first column and data in second or they could be shifted?
Date/time is always first in source data, can't be moved before I get my hands on them
- are columns headers same for each file or they could be different?
Every column header is different for the data (Wing A, Wing B, Wing C, Gas, Electric, etc.) first column in each worksheet is date.
- If different for sources when are they they same for monthly files for the same source?
Monthly files for the same category ( M.S. HVAC, like was linked in this post), will always have the same headers
- is there any naming convention for file names?
Yes, they all follow a "Energy Usage" "Month" "Year" format
- are time intervals the same for all sources (starts from 00:00 with every 15 minutes if not skipped, or that could 00:10, 00:25 as well, etc.)
Time intervals are always the same (0:00, 0:15, 0:30, 0:45, 1:00, etc.) unless an interval is skipped, but will always be a multiple of 15 minutes.
I have discovered a workaround with Pivot Tables. Relating the broken data to a Pivot Table with data for a full calendar with no breaks, forces the broken data to display blanks where there is no data for the power outages (gaps in date/time). I can change the Pivot Table to display zeroes in blank cells, and then copy and paste into the main table. Still time intensive, but nowhere near as bad as before.
I would still like to hear if you have any further thoughts on simplifying this process.