Dynamic Date Range in MS Query using DATEADD
john lilleystone • March 1, 2020
Dynamically shift your dates in Excel and MS Query using DATEADD.
In this screen shot you can see we've explicitly set the date range in MS Query to extract data into Excel. However, this particular report is just a monthly report that I run each month and I don't want to have to edit this date range each time.
In the screen shot below, if we use this syntax, it will dynamically shift the time frame for us when we refresh the data.
In English this is saying look at the months element of the Invoice Date and go back 12, look at today’s date and select everything in between. Substitute 12 for whatever time period you want to go back
Replace ugly Excel errors returned by a formula with something more appropriate and pleasing to the eye.
Use the RANDBETWEEN function in Excel to randomly generate numbers between a specified upper and lower threshold.
Import text files into Excel and use the Text to Columns wizard to separate the data into columns
Extract values or strings from a cell from either the left, middle or right part of the cell
