If we want to see when a workbook was last modified, we can see the information from File Explorer,

or Excel Info

Very handy indeed.

However, if you want the information to be put on a worksheet, there is no formula to retrieve that information directly. No worries, we can get that piece of information using Power Query and it will be super easy (if you are not going to rename your workbook nor move it to a new folder). In fact, it is not difficult at all to turn the solution into a dynamic one. Let me show you step by step.

You may download a sample file to follow along.

Static approach

In this approach, we assume the file won’t be renamed nor be moved to a new location.

Go to Data Tab -> Get Data -> From File -> From Folder

Then browse to the folder that contains the file, followed by Transform Data (or Edit in previous versions of Excel)

At this point, you should see all the files in the folder via Power Query Editor. Did you see that there is a column of “Date modified”?

Click the dropdown on the column header of “Name”, i.e. second column. This is to filter the file that we want:

Note: DO NOT select the one starts with ~$. That’s the phantom file created when the file is opened.

Now. Right-click the column “Date modified” -> Remove Other Columns.

This is all what we want for this exercise. We are ready to Close and Load.

Here we go!

However, there is one major drawback. The result is static. You won’t see the Last modified date/time when the file is saved. We still need a manual Refresh to show the latest information. Nevertheless, in most scenarios, we don’t need that to be updated instantly. It should be good enough whenever the workbook is opened, it is showing last modified date/time.

The Final trick comes to rescue.

Right-click the query (which is now called Power Query in this example) in the Queries & Connections pane, select Properties…

1. Check the “Refresh data when opening the file”
2. OK

As simple as this.

Did you see that? We can set to refresh the query every X minutes. You can set it to one if you REALLY want to…… 😅

Dynamic approach

Dynamic here means the query will work when the file is moved to another location, or even renamed. A few more steps are involved. It’s better to illustrate in a video. Let’s watch it in action!

Not too difficult, is it? 😉