Results 1 to 5 of 5

Thread: Restricting data entry to an End of Month Date

  1. #1

    Restricting data entry to an End of Month Date



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Why do you need to do this? Can't you simply format the datae to display month & year only?
    Hope that helps

    Roy

  3. #3
    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.

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Possibly a PivotTable would be better than using SUMIF. Group results by Month
    Hope that helps

    Roy

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •