62

Is it possible to insert a datepicker in every cell of a column in Google Spreadsheet so that anyone can click (with a single click) on that cell and get a datepicker calendar to select a date?

99

Right-click the selection you want to have the date picker show up for (i.e. single cell, entire row, entire column) and then open data validation. Set Criteria: Date is a valid date and click Save. Now just double-click the cell!

4
  • 3
    This worked and should be set as the answer! Jan 12 '14 at 18:01
  • 1
    Nice solution this guy should get a medal or something.
    – GeneCode
    Nov 20 '17 at 4:26
  • Is there any solution for the mobile app?
    – Ben Carp
    Sep 13 '19 at 19:53
  • Double Click on a cell isn't intuitive. Can I open date picker just on focus? Aug 9 at 9:42
8

The very first answer seems to be working for me.

Step 1: Right Click on cell in which you want to enter a date.
Step 2: Choose Data Validation.
Step 3: Set Criteria to "Date" is a valid date.
Step 4: SAVE
Step 5: Double click on the cell and the date picker will pop-out.

If you want to multiple cells to show the date picker, just Copy the Validated cell and paste it wherever you want to show dates on the sheet.

3
  • 5
    I don't see that this adds anything not already covered by this answer.
    – ale
    Jan 6 '17 at 13:43
  • Did he just copied the answer and posted as his own?
    – GeneCode
    Nov 20 '17 at 4:26
  • Well at least it works and it's put clearly...
    – avia
    Oct 9 '20 at 18:08
1

if I remember correctly you can just set the cells format to date and that will have the same effect

I don't think there is a solution that will meet your exact requirements however you could use some conditional formatting to "cheat" the effect for example

have a default date of 01/01/0001 i.e a date what will never be entered by a user

then conditional format the cells to be font colour the same as the background of the cell.

This way when a user double clicks the cell it will show the date picker but will look like a blank cell until they do so, of course you will have to make sure your other formulas ignore the default date.

2
  • ya i already did it as you said..but i am not getting the datepicker when i click on cell..instead of that, i have to enter date manually after that if i clik on that cell then datepicker appears.
    – user12181
    Jul 19 '11 at 4:31
  • @user12181 see my revision
    – Matt
    Jul 20 '11 at 7:29
-2

If you pre-populate the cell with a random date, double clicking will bring up the date picker.

Source: Google Support: Format numbers in a spreadsheet

2
  • i already went through that link...but i am not pre populating the cell with random date.
    – user12181
    Jul 17 '11 at 18:10
  • but what if the field is not prepopulated. I need something like that whole column shows the datepicker when a cell is selected. Oct 31 '12 at 19:51
-4

The solution would involve a little bit of VBA programming. So you create a date picker and position it in cell A1. Right click on it and choose option View code. VBA window will open and you will see two drop down menus (in the first it will probably be diplayed: DTPicker21). From the second drop down menu choose "CHANGE". his creates an empty procedure for the control's Change event

A procedure to write the selected date into the active cell:

Private Sub DTPicker21_Change()
    ActiveCell.Value = Me.DTPicker21.Value
End Sub

Open the DEBUG mode and choose Compile, then save the file as .xlsm (Excel Macro-enabled workbook). Exit from developer mode.

At the end, use option freeze top row (your date picker box was placed in A1) so while scrolling it will always be visible. select any cell you want to enter date then click date picker and choose desired date.

1
  • 6
    Oliver, thanks for posting your solution. There's one problem though: the OP uses a Google Spreadsheet. The only programming that can be done, is in Google Apps Script. May 11 '14 at 19:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.