Date recognition in Excel cell

D

deecronin1

New Member
Joined
Dec 14, 2018
Messages
6
So 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?

Thanks,
Denise
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hello,

In your worksheet ... your dates are recognized as Numbers ... which is correct ...

in your UserForm ... most probably ... despite their identical visual aspects ... your dates are stored ... as Text ...

You have to make sure to convert them back to numbers ... for example with the CDate() function ...

Hope this clarifies ...
 
Upvote 0
Hi,

Thank you for the prompt reply.

Yes I think that may be the problem. Where do i convert them back to numbers?? Bit of an Excel novice!!

Thanks in advance
 
Upvote 0
=DATEVALUE(cell with text) will change a text date to date format.
=IFERROR(DATEVALUE(cell with text),cell with text) will keep the text if there's a conversion error.
 
Upvote 0


Hi,


Thank you for your help to date. I seem to still be havingdifficulty and am currently unable to resolve the issue.



I’m sure it’s something simple that and that I am just notusing the CDate formula in the correct place.




If I send a copy of my user form would you have a look atit? I would very much appreciate it.




Thanks in advance,


Denise


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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question
Conditional formatting
Replies
3
Views
112
SanjayGMusafir
SanjayGMusafir
S
  • Solved
Copying sheet row when date has been reached
Replies
3
Views
362
saileen
S
S
  • Question
Date formatting issue sheet to VBA Array to Sheet
Replies
2
Views
137
MARK858
MARK858
S
  • Question
Inconsistent behaviour interpreting dates, variously as dd/mm or mm/dd
Replies
3
Views
320
MARK858
MARK858
B
  • Solved
Macro code changing date format
Replies
6
Views
119
Beneindias
B

Forum statistics

Threads
1,214,667
Messages
6,120,821
Members
448,990
Latest member
rohitsomani

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