+ Reply to Thread
Results 1 to 2 of 2

Date format issue

Hybrid View

  1. 01-15-2019, 08:18 AM #1
    deecronin1
    deecronin1 is offline
    Registered User
    Join Date
    01-15-2019
    Location
    Dublin, Ireland
    MS-Off Ver
    2007
    Posts
    1

    Date format issue

    Hi,

    I have created a User From to manage the batch acceptance of stock in my workplace.

    When a new product is received the expiry date is logged...I have set the cells (using conditional formatting) to go red once the product has expired and to go yellow when the product is within 30 days of expiry.

    The problem is however that on inputting the date in the form any date over 12 it doesn't seem to recognise it as a date. So for example if I enter the date 11/12/2018 in the form it will change in the cell in excel sheet to 12/11/2018. But if I enter the date 13/12/2018 it will go in as that on the form but in the cell in excel sheet does not seem to recognise it as a date.

    I have formatted the date in the excel sheet to UK version.

    It may be an issue with how I set up the VBA?

    Is anyone familiar with this issue?

    Below is the data in the VBA if this helps?

    Private Sub Label2_Click()

    End Sub
    Private Sub Label6_Click()
    End Sub
    Private Sub Label7_Click()
    End Sub


    Private Sub txtExpiry_Change()
    End Sub
    Private Sub UserForm_Click()
    End Sub
    Private Sub cboKit_DropButt*******()
    'Populate control.
    Me.cboKit.AddItem "Adenovirus positive control"
    Me.cboKit.AddItem "1% Acid Alcohol"
    Me.cboKit.AddItem "70% alcohol"
    Me.cboKit.AddItem "Alkaline Peptone Water"
    Me.cboKit.AddItem "Clostridium Anaerobic Media"
    Me.cboKit.AddItem "Cryptosporidium control slides"
    Me.cboKit.AddItem "DCA agar"
    Me.cboKit.AddItem "DNA away"
    Me.cboKit.AddItem "EntericBio gastropanel"
    Me.cboKit.AddItem "EntericBio C. difficile"
    Me.cboKit.AddItem "EntericBio S.P.S tubes"
    Me.cboKit.AddItem "EntericBio Norovirus"
    Me.cboKit.AddItem "EntericBio Viralmix"
    Me.cboKit.AddItem "EntericBio GP in house control SHCASAVT"
    Me.cboKit.AddItem "EntericBio GP in house control Giardia"
    Me.cboKit.AddItem "EntericBio GP in house control Cryptosporidium"
    Me.cboKit.AddItem "EntericBio C. difficile in house control"
    Me.cboKit.AddItem "EntericBio Norovirus in house control"
    Me.cboKit.AddItem "EntericBio CPE in house control"
    Me.cboKit.AddItem "Ethyl Acetate"
    Me.cboKit.AddItem "Floq swabs"
    Me.cboKit.AddItem "Lugol's Double Strength Iodine"
    Me.cboKit.AddItem "Methanol"
    Me.cboKit.AddItem "Midi Parasep"
    Me.cboKit.AddItem "Malachite Green"
    Me.cboKit.AddItem "Rotavirus positive control"
    Me.cboKit.AddItem "Rotavirus/Adenovirus Combi strips"
    Me.cboKit.AddItem "Selenite broth"
    Me.cboKit.AddItem "Saline"
    Me.cboKit.AddItem "Sterile Water"
    Me.cboKit.AddItem "Salmonella Polyvalent-H Phase 1&2"
    Me.cboKit.AddItem "Salmonella Polyvalent O Group A-S"
    Me.cboKit.AddItem "Salmonella 4-O"
    Me.cboKit.AddItem "Salmonella 5-O"
    Me.cboKit.AddItem "Salmonella 9-O"
    Me.cboKit.AddItem "Salmonella G-H"
    Me.cboKit.AddItem "Salmonella d-H"
    Me.cboKit.AddItem "Salmonella Vi"
    Me.cboKit.AddItem "Salmonella i-H"
    Me.cboKit.AddItem "S. dysenteriae Polyvalent (1-10)"
    Me.cboKit.AddItem "S. boydii Polyvalent 1 (1-6)"
    Me.cboKit.AddItem "S. boydii Polyvalent 2 (7-11)"
    Me.cboKit.AddItem "S. boydii Polyvalent 3 (12-15)"
    Me.cboKit.AddItem "S. flexneri Polyvalent (1-6, X & Y)"
    Me.cboKit.AddItem "S. sonnei (Phase 1 & 2)"
    Me.cboKit.AddItem "TCBS agar"
    Me.cboKit.AddItem "XLD agar"
    Me.cboKit.AddItem "Yersinia Agar"
    End Sub
    Private Sub cboQcpos_DropButt*******()
    'Populate control.
    Me.cboQcpos.AddItem "Passed"
    Me.cboQcpos.AddItem "Failed"
    Me.cboQcpos.AddItem "N/A"
    End Sub
    Private Sub cboQcneg_DropButt*******()
    'Populate control.
    Me.cboQcneg.AddItem "Passed"
    Me.cboQcneg.AddItem "Failed"
    Me.cboQcneg.AddItem "N/A"
    End Sub
    Private Sub cboQcinitial_DropButt*******()
    'Populate control.
    Me.cboQcinitial.AddItem "AB"
    Me.cboQcinitial.AddItem "AE"
    Me.cboQcinitial.AddItem "AH"
    Me.cboQcinitial.AddItem "AK"
    Me.cboQcinitial.AddItem "AOD"
    Me.cboQcinitial.AddItem "AS"
    Me.cboQcinitial.AddItem "AOS"
    Me.cboQcinitial.AddItem "BOC"
    Me.cboQcinitial.AddItem "BOF"
    Me.cboQcinitial.AddItem "CH"
    Me.cboQcinitial.AddItem "COS"
    Me.cboQcinitial.AddItem "DC"
    Me.cboQcinitial.AddItem "DEMC"
    Me.cboQcinitial.AddItem "DK"
    Me.cboQcinitial.AddItem "DKIR"
    Me.cboQcinitial.AddItem "DL"
    Me.cboQcinitial.AddItem "DS"
    Me.cboQcinitial.AddItem "EDMC"
    Me.cboQcinitial.AddItem "EH"
    Me.cboQcinitial.AddItem "EOS"
    Me.cboQcinitial.AddItem "EW"
    Me.cboQcinitial.AddItem "GAC"
    Me.cboQcinitial.AddItem "HMCE"
    Me.cboQcinitial.AddItem "IOC"
    Me.cboQcinitial.AddItem "JB"
    Me.cboQcinitial.AddItem "JH"
    Me.cboQcinitial.AddItem "LB"
    Me.cboQcinitial.AddItem "LH"
    Me.cboQcinitial.AddItem "LOUB"
    Me.cboQcinitial.AddItem "MAB"
    Me.cboQcinitial.AddItem "MCOG"
    Me.cboQcinitial.AddItem "MF"
    Me.cboQcinitial.AddItem "MM"
    Me.cboQcinitial.AddItem "MOD"
    Me.cboQcinitial.AddItem "MOM"
    Me.cboQcinitial.AddItem "N/A"
    Me.cboQcinitial.AddItem "NOC"
    Me.cboQcinitial.AddItem "OOC"
    Me.cboQcinitial.AddItem "PC"
    Me.cboQcinitial.AddItem "RJ"
    Me.cboQcinitial.AddItem "ROC"
    Me.cboQcinitial.AddItem "SB"
    Me.cboQcinitial.AddItem "SC"
    Me.cboQcinitial.AddItem "SCR"
    Me.cboQcinitial.AddItem "SOC"
    Me.cboQcinitial.AddItem "SOD"
    End Sub
    Private Sub cboUse_DropButt*******()
    'Populate control.
    Me.cboUse.AddItem "AB"
    Me.cboUse.AddItem "AE"
    Me.cboUse.AddItem "AH"
    Me.cboUse.AddItem "AK"
    Me.cboUse.AddItem "AOD"
    Me.cboUse.AddItem "AS"
    Me.cboUse.AddItem "AOS"
    Me.cboUse.AddItem "BOC"
    Me.cboUse.AddItem "BOF"
    Me.cboUse.AddItem "CH"
    Me.cboUse.AddItem "COS"
    Me.cboUse.AddItem "DC"
    Me.cboUse.AddItem "DEMC"
    Me.cboUse.AddItem "DK"
    Me.cboUse.AddItem "DKIR"
    Me.cboUse.AddItem "DL"
    Me.cboUse.AddItem "DS"
    Me.cboUse.AddItem "EDMC"
    Me.cboUse.AddItem "EH"
    Me.cboUse.AddItem "EOS"
    Me.cboUse.AddItem "EW"
    Me.cboUse.AddItem "GAC"
    Me.cboUse.AddItem "HMCE"
    Me.cboUse.AddItem "IOC"
    Me.cboUse.AddItem "JB"
    Me.cboUse.AddItem "JH"
    Me.cboUse.AddItem "LB"
    Me.cboUse.AddItem "LH"
    Me.cboUse.AddItem "LOUB"
    Me.cboUse.AddItem "MAB"
    Me.cboUse.AddItem "MCOG"
    Me.cboUse.AddItem "MF"
    Me.cboUse.AddItem "MM"
    Me.cboUse.AddItem "MOD"
    Me.cboUse.AddItem "MOM"
    Me.cboUse.AddItem "NOC"
    Me.cboUse.AddItem "OOC"
    Me.cboUse.AddItem "PC"
    Me.cboUse.AddItem "RJ"
    Me.cboUse.AddItem "ROC"
    Me.cboUse.AddItem "SB"
    Me.cboUse.AddItem "SC"
    Me.cboUse.AddItem "SCR"
    Me.cboUse.AddItem "SOC"
    Me.cboUse.AddItem "SOD"
    End Sub
    Private Sub cboInuse_DropButt*******()
    'Populate control.
    Me.cboInuse.AddItem "Yes"
    Me.cboInuse.AddItem "No"
    Me.cboInuse.AddItem "Awaiting First Use"
    End Sub

    Private Sub cmdAdd_Click()
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Lotnumbers")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
    .Cells(lRow, 1).Value = Me.cboKit.Value
    .Cells(lRow, 2).Value = Me.txtLotno.Value
    .Cells(lRow, 3).Value = Me.txtExpiry.Value
    .Cells(lRow, 4).Value = Me.cboQcpos.Value
    .Cells(lRow, 5).Value = Me.cboQcneg.Value
    .Cells(lRow, 6).Value = Me.cboQcinitial.Value
    .Cells(lRow, 7).Value = Me.txtFirst.Value
    .Cells(lRow, 8).Value = Me.cboUse.Value
    .Cells(lRow, 9).Value = Me.cboInuse.Value
    End With
    'Clear input controls.
    Me.cboKit.Value = ""
    Me.txtLotno.Value = ""
    Me.txtExpiry.Value = ""
    Me.cboQcpos.Value = ""
    Me.cboQcneg.Value = ""
    Me.cboQcinitial.Value = ""
    Me.txtFirst.Value = ""
    Me.cboUse.Value = ""
    Me.cboInuse.Value = ""
    End Sub
    Private Sub cmdClose_Click()
    'Close UserForm.
    Unload Me
    End Sub
    Private Sub UserForm_Initialize()
    txtExpiry.Text = Format("mm/dd/yyyy")
    txtFirst.Text = Format("mm/dd/yyyy")
    End Sub

    Thanks,
    Denise
    Reply With QuoteRegister To Reply
  2. 01-15-2019, 08:25 AM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Date format issue

    Whoah, whoah, whoah,

    Welcome to the forum, but we have a rule about putting VBA code in code tags to make it easier to read. Please do so.

    In addition are you sure there is an essential need to use VBA, particularly something like this where probably some standard Excel functionality finished off with perhaps a few lines of VBA code?

    Upload the wrkbook and tell us what the overall aim is. i.e. what you start with, what you want to enter and what you want to happen. By that I don't mean tell us what your existing macros are doing, let us decide that. We generally prefer to go back to first principles where it seems clear that what you have may not be the most efficient,
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Date format issue shows as Jan 8, 2015 (Non date format) but want DD/MM/YYYY - Help
    By seash in forum Excel General
    Replies: 4
    Last Post: 04-29-2015, 08:36 AM
  2. Date Format, CSV issue
    By Chris Acheson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2014, 10:52 AM
  3. [SOLVED] Issue with date format when comparing two date values (I'm in Australia)
    By aaron.irvine in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2013, 01:13 AM
  4. DATE FORMAT issue
    By [email protected] in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2006, 03:55 AM
  5. Date format issue
    By Ian in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-28-2005, 01:25 PM
  6. Date format issue
    By DanielHurtubise in forum Excel General
    Replies: 2
    Last Post: 09-21-2005, 02:05 PM
  7. Date Format Issue
    By Mikeice in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-02-2005, 06:54 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1