Why do you need to do this? Can't you simply format the datae to display month & year only?
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
Why do you need to do this? Can't you simply format the datae to display month & year only?
Hope that helps
Roy
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.
Possibly a PivotTable would be better than using SUMIF. Group results by Month
Hope that helps
Roy
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.
...or you could simply use EOMONTH within your SUMIF formula.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
Bookmarks