Help with DateADD function

  • I have been working on a database to collate a range of activities within a prison setting, that measures those activities against Equalities and Diversity protected characteristics, i.e race gender etc etc

    I have attached a sample sheet, with regards to the one issue I am currently having, because of the nature of data protection laws, I have populated this with some fictitious names and details, This is an Macro Enabled .xlsm file in Excel 2013.

    On the attached sample are four sheets

    "Home" ... starting point, contains links to the two userforms

    "Lists" ... contains a selection of Named Ranges, for use in the comboboxes on the two user forms

    "Prisoners" ... stores the basic data that populates the other userforms (accessed by clicking the "Residents Protected Characteristics" button on the home sheet)

    "SCRB" .... this stores data about an event ..... (accessed by clicking the add "Add / update SCRB" button on the home sheet"

    It is the userform F_SCRB that I am having issues with.

    You load a prisoners details into the userform by clicking into the combobox "CbFind" located at the top of the userform next to the text label that reads "Add SCRB". There are number of textboxes, that are hidden from view that store the prisoners details, that then populate the spreadsheet, when you add or save the record, all that is visible is their nomis number (Prisoner Number) forename and surname ... don't worry about those details for now

    I have a text box "TxDate" that records the date that an SCRB event occurs, this is formatted as below

    Private Sub TxDate_AfterUpdate()  ' changes date box into dd-mmm-yy value
    Dim dDate As Date
        dDate = DateSerial(Year(Date), Month(Date), Day(Date))
        TxDate.Value = Format(TxDate.Value, "dd mmm yy")
    On Error GoTo 0
    End Sub

    Likewise their is a review date "TxReview" that is similarly formatted, so I won't repost the code.

    The issue I have is a combobox that is supposed to populate "TxReview" the coding for that is

    If you look at the example that I have loaded onto thea userform accessed by clicking into combobox "CbFind" .... next to the textlabel that reads "Update" ...

    TxDate value is 2nd Feb

    CbDuration value is "3 months"

    TX Review value is 5th Feb .... but should be 2nd May

    I hope the above make sense, please feel free to play around with the userforms attached and let me know if there is any reason the dataAdd function in "Private sub CbDuration_Change" is not working correctly

    Thanks in advance

    Iain C

  • Excel has a habit of interpreting dates as mm dd yy (even if that is not your local date setting).

    You generally need to code around it, maybe try:

  • Thank you Gisjmo

    This seems to be a lot more stable, I will play around with adding records etc .... if it works all the time, I will report my issue as fixed.

    Iain C

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!