Search

Creating Dynamic Dates in Excel


As we know in Excel the dates are numbers and at front end we can see a date, but in the back end it a number. The Excel calendar starts from 1st January1900. If we type 1 in Excel and change the format to date format by pressing Ctrl shift 3 (Shortcut is Ctrl # but to get # we need to press shift and 3), we will get 1st Jan 1900 as the output. Similarly, we can check for any number. Since we have number at the backend, we can use all mathematical formulas for date. This is why the dates in Excel become so useful and interesting.


In this article we will discuss how we can create dynamic dates which get updated automatically on daily basis. This we will achieve by using TODAY() function.


Here is the short video explaining the feature in detail.




The TODAY() function has great utility in reports with changing dates. It is also helpful for calculating expiry dates of products, due dates for invoices and has various other applications.


In the example below we see a report which gives sale of soft drinks today, which is like everyday and then the sale day before and the sale for the same soft drink on the same date last month. (Click on the link to download Excel File: https://bit.ly/2ZpIXjg)



The figures in this report has to be updated every day, but at times we may forget to change the date. It would be great if we automate the date updation and here our TODAY() function becomes very useful.

In the column C we can see the date is 27th Oct 2021, the day we are writing this artice. This has been taken using the TODAY() formula. The advantage here is that the date will update every day on its own. For Yesterday we have again used the formula as seen in cell B5, that is from TODAT() we have subtracted one.

For last month same date we have substracted 30 from C6 (which has TODAY() function)

To get last month Same day we have subtracted 31 from TODAY().


SYNTAX

The syntax of TODAY() formula is very simple. We have to just type=TODAY open bracket and close bracket and enter. That is it, very simple yet very useful.


APPLICATION

We will see some applications of TODAY function.


Example-1

In example below we can see a table where the expiry dates of products are entered in column A.

In column I "Days To Expiry" is calculated using TODAY() function. Formula used is Expiry Date minus today’s date. In column J we can see the formula applied in column I. The Best part is it gets updated automatically everyday.


Example-2

In example below we can see a table where the Date of birth are entered in column A and in column B age in days is being calculated and updated on daily basis.


In column I the age is being calculated using TODAY( ) function. Formula used is today’s date minus the date of birth, which is given in column H. The age here gets updated everyday.

Days left for retirement can be calculated and we can fix alerts when it comes to say 3 days left for retirements. This gets automatically updated and if we apply conditional formatting, it will get highlighted for employees getting into that threshold period.


Similarly TODAY() function can be put to many uses and the main advantage is in situations where we want to check or get a trigger as in case of expiry of products, retiremt, payment dues, payables, receivables alert and many more.


Parting TIP- The shortcut for TODAY() function is Ctrl + ; (Ctrl and semi colon)


Happy Learning!!!

20 views0 comments

Recent Posts

See All