Formula Date Format/Input Error?

[pure vito]

Hi All,

I have been struggling keep this formula working as intended, I've spent a lot of time scratching my head over this,

The Issue is with column D and the date if I manually type the date it works fine, if I use a formula in i.e. =Now() it sometimes works

Now I wish to input the date via a user form a textbox this I have completed and the date from the textbox is pasted to D2:D100 but for some reason the formula will not work
because of this date input, I have include a copy of the data unfortunately L2BB does not work with my work computer,

I really can't work this out, can anyone assist please, Thanks in advance.

=IFERROR(IF(INDEX('Data Sheet'!$C$9:$ND$59,MATCH(C2,'Data Sheet'!$B$9:$B$100,0),MATCH(D2,'Data Sheet'!$C$9:$ND$9,0))= "","Yes",""),"")

MrExcel.xlsx

 

[Alex Blakenburg]

2 things.
• Your first match runs to row 100 but the Index range only runs to 59
• Your date column D is formatted as Date but the values are actually entered as Text.
How is your data getting in to that sheet ?
You can convert it selecting column D, then Text to Columns > Delimited > Finish
But you will still need to address how you are getting the data so you don't have to do that each time.

 

[pure vito]

Thank you Alex for your assistance, great advise

Here is the VBA I am using to input the date, the worksheet is ("Available")

Again thanks Alex,

Private Sub showall_Click()
' ---------- Show All ---------------

Dim sh As Worksheet
Set sh = Sheets("JobRequirements")
Dim iRow As Long
iRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
Roledate.Value = Format(Roledate.Value, "dd/mm/yyyy")
Worksheets("Available").Range("D2:D100").Value = yourjobs.Roledate.Value
Worksheets("Rolecheck").Range("F1").Value = yourjobs.Roledate.Value
Worksheets("JobRequirements").Range("E2:E100").Value = yourjobs.Roledate.Value

'to populate listbox  from Database sheet
   With Me.ListBox1
       .ColumnCount = 5
       .ColumnHeads = True
       .ColumnWidths = "120,120,120,120,120"
       .RowSource = "JobRequirements!A2:E" & iRow
       Sheets("Rolecheck").Visible = False
       Sheets("Home").Select
   End With
   'Now run & See
End Sub

 

[Alex Blakenburg]

So in this line Roledate.Value = Format(Roledate.Value, "dd/mm/yyyy"), you are converting the date to TEXT.
I can't see where you are getting Roledate from. If it is from a Cell try using RoleDate.Value2 and don't use Format in VBA just Format the Column to "dd/mm/yyyy"

 

[pure vito]

Sorry Alex I should have sent the full document from the start

Holiday Tracker fresh start.xlsm

The user form is in "Home" button names Your Roles

I have removed that line but it doesn't seem to work,

 

[Alex Blakenburg]

See if this works for you.

Private Sub showall_Click()
' ---------- Show All ---------------

Dim sh As Worksheet
Set sh = Sheets("JobRequirements")
Dim iRow As Long
iRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row

' ----- Changed Section -----
Dim dtRoleDate As Date
dtRoleDate = CDate(Roledate.Value)

Worksheets("Available").Range("D2:D100").Value2 = dtRoleDate
Worksheets("Rolecheck").Range("F1").Value2 = dtRoleDate
Worksheets("JobRequirements").Range("E2:E100").Value2 = dtRoleDate
' ----- End Changed Section -----

'to populate listbox  from Database sheet
   With Me.ListBox1
       .ColumnCount = 5
       .ColumnHeads = True
       .ColumnWidths = "120,120,120,120,120"
       .RowSource = "JobRequirements!A2:E" & iRow
       Sheets("Rolecheck").Visible = False
       Sheets("Home").Select
   End With
   'Now run & See
End Sub

 

[pure vito]

Works like a dream Alex, and it's not the first time you've helped me out thank you very much indeed

 

[Alex Blakenburg]

You're welcome. Happy to help.