Blog

The Blog is a collection of News, Blog posts, technical tips and tricks and other writings from my sphere of interest.
- Esben Jannik Bjerrum

Jun

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.

Excel drop-down with date

Make a drop-down in Excel in 4 easy steps

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.

Excel_data_validation

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.

Excel_select_list

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.

Excel_Format_as_Date_2

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.

Excel_dropdown_with_date

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.

Configure the Data Validation to give information message only

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.


Comment

  1. Anonymous
    August 15, 2016 at 16:07 Reply

    It’s difficult to find knowledgeable folks on this issue, but you sound
    like you understand what you’re talking about!
    Thanks

  2. Anonymous
    August 20, 2016 at 10:27 Reply

    Your place is valueble for me. Thanks!

  3. Anonymous
    August 21, 2016 at 03:05 Reply

    Greetings! Really helpful advice on this article! It’s the little changes that make the largest changes.

    Thanks a lot for sharing!

  4. Anonymous
    August 24, 2016 at 05:41 Reply

    Good information. Blessed me I reach on your site by accident, I bookmarked
    it.

Leave a Reply

Your email address will not be published. Required fields are marked *