Date format issue

D

deecronin1

New Member
Joined
Dec 14, 2018
Messages
6
Hi all,

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?
Code: 
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
 
Last edited by a moderator:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Denise

When putting dates from textboxes into cells you need to convert them from 'text' dates to actual dates.

One way to do that is to use DateValue.
Code: 
.Cells(lRow, 3).Value = DateValue(Me.txtExpiry.Value)
 
Upvote 0
When you put it into a cell try using CDate eg:

Code: 
Range("A1").Value = CDate(TextBox1.Text)
 
Upvote 0
Hi,

Thanks for the prompt response, where exactly in my form should I insert the above code??

Many thanks,
Denise
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Page break issues when exporting to pdf from userform (VBA)
Replies
0
Views
897
auglocqnuk
A
I
  • Question Question
Combo Box & Completing Form with concat additions
Replies
0
Views
500
iamblue91
I
M
  • Question Question
Excel VBA - calendar date pick up: problem with code
Replies
4
Views
2K
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question Question
Workbook Open
Replies
6
Views
965
Logit
Logit
dreid1011
  • Question Question
For loop and stuffing was working, now it is misbehaving.
Replies
1
Views
649
dreid1011
dreid1011

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top