Simple solution:
On another worksheet in the same workbook:
In cell A1 enter =today()
in cell A2 enter =A1-1
autofill down to row 30 or 31 (however many days you want)
While still on this sheet, create a named range that represents that range
of cells, say A1:A31. For example, call it MyDateList
On your main worksheet, go back to the cell where you want the date. apply
data validation, pick "list", and enter the name of your named range
(=MyDateList)
Then when you click that cell you will get drop-down list of dates, and the
dates will always show a rolling 31 days.
If you need something different (like only show current month, or also
include future dates), please specify your needs.
I didn't give explicit instructions on how to set up the named range or data
validation, as the toolbar or menu selections vary slightly from 2003 to 2007
and you didn't specify which version you are using.