Restricting data entry to an End of Month Date

[kogersdad]

Is there any way to restrict a cell's value to an End of Month Date only? For example, in Column D of a spreadsheet, I would like other users to enter a date but they can only enter an end of month date (1/31/2013, 2/28/2013, etc).
I realize I could perform this by creating a drop-down list but I would like to avoid this option if at all possible.

I would like to take it even further by allowing them to enter any date and then have the spreadsheet convert the entered date to the EOMonth date in the same cell. Example, if they entered 1/28/2013 in D1, it would appear as 1/31/2013 in D1 (same for each other day in each month).
In other words, can Excel "round up" to the end of month automatically in the same cell as the entered date?

thanks
Tim

[royUK]

Why do you need to do this? Can't you simply format the datae to display month & year only?

[kogersdad]

I need it to be the exact date due to a SUMIF formula that refers to a specific date (1/31, 2/28, 3/31, etc). Formatting it would not make it specific enough.

[royUK]

Possibly a PivotTable would be better than using SUMIF. Group results by Month

[JeffreyWeir]

Why not use another column to turn their user input into an End of Month date, and then use that to feed your SUMIF formula? You can do this using the EOMONTH function e.g. =EOMONTH(C2,0). That's what I would do.

Otherwise you could use VBA. Assign a named range to the input range in column C called MonthEnd, and then put this in the sheet code module of the particular input sheet that users enter the date into.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [MonthEnd]) Is Nothing Then
Application.EnableEvents = False
    Target.Value = WorksheetFunction.EoMonth(Target, 0)
Application.EnableEvents = True
End If
End Sub

...or you could simply use EOMONTH within your SUMIF formula.