There’s a story making the rounds, about a guy who rated all his online dating prospects, by using an Excel worksheet.
While I’m sure we could all learn a thing or two from that guy’s file, this article is about a different kind of dates – calendar dates.
Calendar Dates in Excel
Calendar dates might not be as exciting, but unlike romantic dates, you can use data validation to help keep them under control in Excel.
Entering Period End Dates
Last week in the Daily Dose of Excel blog, Dick Kusleika was looking for a more efficient way to enter a Period Ending date in his time sheet.
Every two weeks he sets up a new workbook, and has to add two weeks to the previous end date.
Sometimes it’s easy to do that kind of math in your head, but if you’re rolling into a new month, the calculation can be trickier.
So, Dick changed the date cell to a formula, and just added 14 to that calculation every two weeks.
There were a few suggestions for solving the problem, but Dick wanted something simple and static — the date cell couldn’t change, if you opened the file on a different date.
Create a Dynamic List of Dates
My suggestion was to create a drop down list of Period End dates, and select the next date from that list. With some formulas in the background, the list of valid dates would update automatically.
However, when you select one of those dates in the Period End cell, it’s a static entry in that cell.
Create List of Dates
The first step is to create the list of dates, based on Dick’s original Period End date – October 13, 2010.
- On Sheet2, put the starting date in cell C2: =DATE(2010,8,13)
- In cell C3, enter a formula to calculate the current date: =TODAY()
- In C6, enter this formula, to calculate the current pay period’s week end:
=C2+INT((C3-C2)/14)*14
- In C5, subtract 14 days, to calculate the previous period end date: =C6-14
- In C7, add 14 days, to calculate the next period end date: =C6+14
Name the Date List
Next, you’ll name the date list, so you can use it in a data validation drop down.
- Select cells C5:C7, and click in the Name Box, to the left of the Formula bar
- Type a one-word name for the list – DateList – and press the Enter key.
Create the Drop Down List of Dates
Back on the Time Sheet, you’ll add a data validation drop down list in the date cell.
- On the Time Sheet, select the Period End date cell.
- On the Excel Ribbon, click the Data tab, and click Data Validation.
- From the Allow drop down, click on List
- In the Source box, type an equal sign and the list’s name: =DateList
- Click OK to close the data validation dialog box.
Test the Drop Down List of Dates
On the Time Sheet, the Period End date cell now has a drop down arrow.
- Click the arrow, to see the current list of dates, and click on a date to select it.
The selected date is entered in the cell, as a value, not a formula. Even when the list of dates changes, the selected date will not be affected.
More Examples
Please visit the Contextures website for more examples of Excel Data Validation for dates.
__________________