Today() function is one of volatile functions in Excel. Volatile functions are recalculated each time any cell in the spreadsheet is changed. Some other actions can also cause recalculation, such as renaming sheets, inserting columns, deleting rows, etc. If you have many cells in Spreadsheet that are referring to cell that contains Today() function or you have many cells using Today() function, everything in your file will slow down. Constant recalculations will make working in such file really unpleasant experience.
Today() function will change its result only when the new day arrives. Because of this we can find another way to get today’s date. There are actually several ways to accomplish this.
In VBA, if we place “Application.Volatile” as the first line in our UDF (User Define Function), that function will become volatile. We will not do that. That way we can make VBA UDF function that is not volatile and it is replacement for Excel today() function. This function will refresh itself each time we open the file.
|User now just have to type |
into spreadsheet and he will get
today’s date inside that cell.
Power Query Solution
We will first make a query that only returns today’s date.
|We will load this query into |
spreadsheet so that we have
today’s date in cell A3.
We will then set the option so that query is refreshed every time the file is opened.
|In the pane with the queries (1), we should right click on our query and choose its Properties (2). In the new dialog we want to check option “Refresh data when opening the file” (3).|
Possibility of error
There is a small problem with VBA and Power Query solutions. If we open the file just before a midnight, when the midnight pass, new day will arrive, but our date will not change. For VBA solution, we would need to enter the cell with F2 and press Enter ( F9 and Calculate Now would not work on UDF function ). For Power Query solution we would need to Refresh our query.
Closing and opening the file would also give us new date, or we can just turn off our computer and go to sleep before midnight.
Power Automate Solution
VBA and Power Query will not work for Excel online. We can use Power Automate to change date in one cell every day in midnight. We will combine Power Query solution and Power Automate solution, so that it doesn’t matter if file is in cloud or on user’s computer. First, we will change our query because Power Automate needs one more column. We are going to add “Key” column to our Power Query table.
Now we can create flow that will change date in cell B3 every day in midnight.
|We click on “Create” button (1) for the new flow. Then we select “Scheduled cloud flow” (2). We will be offered dialog where we give our flow name (3), we can decide that from now, each day the flow will run (4,5). Next, we will get diagram view with “Recurrence” as the first step (6). As a next step we will add “Update a row” (7). Now that we have both our steps (8), we can fill necessary data. |
In Recurrence step we just have to show advanced options (9) and decide what time zone should be used (10). In “Update a row” step we have to write where is our file and what is its name (11,12). (13) is the name of the declared table in which we are going to insert today’s date. “Key” column is considered as primary key column so we have to give a value for primary key which defines row where we want changes to happen (14).
Name of declared table is “pqToday”.
That row is made of fields. Those fields will appear at the bottom (15). We have only one field to fill. In this field we will write:
This code will take current date according to given time zone and it will format it. This date will be written to our Excel file in cell B3.
For some reason step “Update a row” wasn’t able to find my Excel file when I was using XLSM extension. It seams that file has to be XLSX.
Sample file can be downloaded from here: