By Mannix CarneyHandling differently formatted dates in Excel can be a real pain.
This example is based on a time sheet problem that I came across. I was trying to combine time sheet data from different time sheet systems. One system cut off the weekend on a Friday of every week and the other system cut the weekend off on the Saturday of every week.
This set of dates finish on a Friday
This set of dates finish on a Saturday
I had to combine the two time sheet sources to a unified date. This was a small thing, but as I had to do the report once a week I found correcting the issue each week time consuming. A few times I was very busy with other tasks and I had to work over the weekend to get the task done.
Enter Power Query to the rescue
In the following steps, I will show you how I aligned the week ending dates for both data sources and then combined the data into a single source that I could make reports from.
Once this solution was setup once, I just had to drop the new data in the correct folder once a week and Power Query took care of the rest for me.
The Excel file that this example is based on is available here: Time Sheet Example
Align the Dates using Power Query
The first step is to select the data with Power Query using the Data tab. The data can either be selected as a table or selected by referring to a file where the data is stored. In this example I select the table and click From Table/Range.
Click yes to the table having headers.
Rename the Query Saturday
Now Change the Weekend to Date only
Click close and load to.. And select Create Connection Only
Now Do the same with the Friday week Ending data.
When You are finished you should have two connection only queries like this.
Now double click on Friday to open Power Query.
You now want to move the Friday Week Ending Dates forward one day so that they line up with the Saturday week ending dates. Once they are both aligned you can combine the two data sources and make your reports from them.
Highlight the Week End column and click the Add Column option at the top of the page. In the Custom Column Section Inset the following code:
Click the Date option at the top and then select Week – End of Week.
All the Friday Week End Date are now moved from Friday to Saturday throughout the timesheet entries.
Remove the Original Week End Column
Rename the new column Week End and drag it to column 1.
Now that the dates are aligned it’s time to join the two tables together. Right click on the grey column to the left of the Power Query screen. Select New Query – Combine – Append Queries as New.
Select Friday as the first table and Saturday as the second table and click OK.
The two time sheet tables are now joined together with the same week ending date. In order to use you combine data in a report, click Close and Load to… and this time click load to a table in a new worksheet.
The Time sheet data is now ready for you to use!
The best part about this approach is that once it is setup, you just put the updated time sheet data into the correct data and press refresh. Seconds later your data is updated and ready to use.