Excel tip #1: How to add a date stamp drop-down in 4 easy steps.
Sometimes it comes in handy to be able to note a date or time quickly in Excel. This happens often when using Excel sheets for logging purposes and registration of laboratory experiments and results. This post illustrates how to make a drop-down with todays date where the entered date will not update automatically.
Data validation for drop-down menus
For this purpose the data validation tool will be used. The data validation tool is a tool that can be used to set limits for what can be entered into a cell, so only valid numbers can be entered into the cell. By using the ”list” limits and set that to a cell containing the wanted current date, the result is that a drop-down will appear when the mouse pointer is hovered over the cell. Choosing the date will write the date into the cell.
In this example we have a thought up situation where we want to write notes about some laboratory samples and mark the notes with the current date.
Date drop-down in 4 easy steps
1: First the formula =Today() is entered somewhere in the workbook. I usually use a separate worksheet called logic or lists for this purpose.
2: Next, the range of cells that should have the drop-down are selected and the Data Validation tool selected from the menu bar Data tab.
3: Here the list entry is selected from the data validation tool and the ”=Today()” formula we entered before is selected. If the time is of interest, use the formula ”=Now()”. The In-cell dropdown option should be tagged.
4: Format the cells to use the wanted date formal. Right click with the range selected and choose ”format cells”. Choose date and find the wanted date format.
Now the there will be a drop-down besides the cell when the mouse pointer is over one the cells. This can save time and mis entering of dates.
Further Configuration of the drop-down behavior
The data validation can be further configured. I usually change the Error Alert to not stop the user from entering a date that doesn’t match todays date, but rather inform that this is the case.
Additional info on using date and time in Excel.
If the formatting of the cells is not selected as date, instead the cells will contain a number. Excel internally handles date and time as days since the zeroth of January 1900. So the first of January that year is day 1. Fractions are then the time of the day. 0.5 thus corresponds to 12 noon. Time can be entered directly into cells using the the HH:MM:SS format and then used in calculations afterwards and letting Excel keep track of the fact that there is 60 minutes on an hour and 60 seconds in a minute.
If you like the post or have feedback (More/less detailed steps, too simple topic? etc.) drop me a note as a comment 😉 You are also welcome to suggest topics/problems.