Searching between dates with Userform data

[shim85]

Hi,

I've got a userform that has a textbox that the date is entered along with other variables.

I then want to search between the dates to count occurrences of a specific word/value.

It works if I manually put the date in the column, but if the date has been entered via the userform it doesn't work.

Ive looked at the format of the cells that the date goes in, and the date range that I want to search in.

Is there any way around this?

[simarui]

it would be helpful if you uploaded an example workbook showing your problem and desired results. my first guess is the textbox input is reading your "date" as text or as a literal number (3/20/2018 = 7.4331e-5 which represents 12:00:06 AM on 0-jan-1900). there is probably a way to change your textbox to recognize that it is a date or to use vba to convert the text input into a date.

[shim85]

Basically, when entering the date on the userform, it inputs the date and the other variables in a spreadsheet.

Now if I were to search within a date range to find the occurrences, it doesn't add them up, however if I type it in manually, it will.

Unfortunately I can't upload the file with the userform as it says the file is too big.

However this is the code:

Private Sub cmAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(Sheets("Welcome").Range("b3").Value)

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter the Date"
Exit Sub
End If

With ws

.Cells(iRow, 1).Value = Me.txtDate.Value
.Cells(iRow, 2).Value = Me.cboType.Value
.Cells(iRow, 3).Value = Me.txtTimeH.Value
.Cells(iRow, 4).Value = Me.txtTimeM.Value
.Cells(iRow, 5).Value = Me.txtOC.Value
.Cells(iRow, 6).Value = Me.txtReason.Value


End With
Me.txtDate.Value = ""
Me.cboType.Value = ""
Me.txtTimeH.Value = ""
Me.txtTimeM.Value = ""
Me.txtOC.Value = ""
Me.txtReason.Value = ""
Me.txtDate.SetFocus

End Sub

and then the formula to search within the date range is:
=COUNTIFS(B4:B10000,R1,A4:A10000,">="&O3,A4:A10000,"<="&P3) which works fine, if the date is put in manually and not from the userform.

Any help would be appreciated.

[simarui]

i just built a quick and dirty mock-up... not having that issue. don't really see anything substantively different in the code... possibly it could have to do with MM/DD/YYYY vs DD/MM/YYYY formatting?

[simarui]

"You can also right-click on the UserForm Toolbox and select Additional Controls, where you should find a reference to the Microsoft Date & Time Picker controls. They're pre-formatted to accept Dates/Times only. The first displays a calendar control, the second a spin box."


https://www.mrexcel.com/forum/excel-...te-format.html

[shim85]

So I've mocked up a quick sample, the premise is the same, but when I enter the date on the userform and it will input the information on sheet2, but it doesn't count the occurrences of the number 1.

I hope this helps explain what im trying to do.

[simarui]

i'm getting an error trying to download that workbook... try again? for some reason it looks like it uploaded with an .xls-2.xlsx extension... not sure what that's about.

[shim85]

My apologies, hope this works

[simarui]

yea definitely looks like it's a MM/DD/YY vs DD/MM/YY formatting issue... not sure how much i'm actually going to be able to help since i'm on US settings - inputting 3/15/18 into the txtdate text box works for me, and the value currently populating A3 when i download your workbook is "15/3/18" and it's reading as text.

trying that link i posted above might help - use additional commands in your userform to show a date-selector instead of a text box. that way excel should definitely return a date value and formatting shouldn't really matter.

https://www.mrexcel.com/forum/excel-...te-format.html