You don't specify which version of Excel you are using, but there is a built in Calendar Control you can utilize along with a small bit of VBA coding. The calendar does not reside in a cell, but can be used for any cell you select.
For Excel 2003, and prior.
==================
Assume your date column is column A. If it is not, then change the 1 in 'If Target.Column = 1 Then' (below) to reflect your date column, i.e. column F would be 6, column Z would be 26, column AA would be 27, etc.
Open your workbook and go to Insert > Object.
When the 'Object' window opens, scroll down to, and select Calendar Control 11.0.
A calendar with the current date select will be inserted onto the worksheet, and a small toolbar (Design Mode) will appear.
Click the Triangle, Pencil, Ruler icon to access Design Mode.
Click the Calendar Control and drag it to the position on the worksheet that you wish it to be displayed when activated.
Double Click the Calendar Control and the VBE will open displaying:
Private Sub Calendar1_Click()
End Sub
Delete these two lines and copy and paste the two event handlers below into the editing area you just cleared:
Private Sub Calendar1_Click()
ActiveCell.Value = Me.Calendar1.Value
Me.Calendar1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Me.Calendar1.Visible = True
Else
Me.Calendar1.Visible = False
End If
End Sub
Close the VBE, returning to the worksheet.
Click the Triangle, Pencil, Ruler icon again to exit 'Design Mode' and close that toolbar.
Click any cell in column A and the calendar will appear. Select any date you wish to enter in the active cell in column A and the date will be entered in the active cell and the calendar control will close.
Click another cell in column A, select a date, and click 'OK'.
For the other worksheet, simply refer to the dates in the worksheet containing the calendar using the sheet reference in your formula. For example:
In Sheet2, A1 enter:
=Sheet1!A1
==================
For Excel 2010:
===========
If the Developer tab is not visible, go to File > Options and select 'Customize Ribbon'.
In the Main Tabs section to the right, check the 'Developer' check box to show that tab and click 'OK'.
Select the Developer tab and click the 'Insert' drop down in the Controls group.
In the ActiveX controls group, click the bottom right icon (hammer & wrench) to show more controls.
Select the Calendar Control 11.0 icon.
From here the process is the same as above.