Restricting data entry to an End of Month Date

kogersdad

New member
Joined
May 2, 2012
Messages
17
Reaction score
0
Points
0
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​
 
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.
 
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.
Code:
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.
 
Back
Top