date validation should not be > today()

R

ramserp

Board Regular
Joined
May 1, 2009
Messages
62
Hi Friends,

I have string in sheet source which has date part, that date part should not be >today(). When I extract date from source sheet to destination sheet, if the date part has the date >today() it should not load into destination sheet.

Source sheet data

<TABLE style="WIDTH: 80pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=107 border=0 x:str><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=107 height=17>Exp. No</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>071620009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>15252009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>09252009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>13162009_n2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n3</TD></TR></TBODY></TABLE>

I copied my code here for your reference. Please give me the appropriate solution how to put conditon for this.

Sub exp()
Dim v_counter As Integer
Dim v_expnum As String
Dim v_lastrow As Integer
Dim v_date As Variant
Dim v_success As String
v_counter = 2
v_success = "YES"
v_lastrow = Sheets("Source").Range("A65536").End(xlUp).Row
For v_counter = 2 To v_lastrow
v_expnum = Sheets("source").Range("A" & v_counter).Value

v_date = Split(v_expnum, "_")
If IsEmpty(v_expnum) Then
v_success = "NO"
ElseIf Not IsEmpty(v_expnum) And v_success = "YES" Then
If Len(v_date(0)) = 8 Then
' assigning day,month and year to the 3 different variables
v_year = Mid(v_expnum, 5, 4)
v_month = Left(v_expnum, 2)
v_day = Mid(v_expnum, 3, 2)

If v_month < 1 Or v_month > 12 Then
v_success = "NO"

ElseIf v_month >= 1 Or v_month <= 12 Then
Sheets("Destination").Range("A" & v_counter).Value = DateSerial(v_year, v_month, v_day)

End If
Else
v_success = "NO"
End If
End If
Next v_counter
End Sub


Thanks & Regards
Ramesh
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Good Morning,

I think you can use below updates:


Code: 
Sub exp()
    Dim v_counter As Integer
    Dim v_expnum As String
    Dim v_lastrow As Integer
    Dim v_date As Variant
    Dim v_success As String
    Dim v_SampleDate As String
    
    
    v_counter = 2
    v_success = "YES"
    v_lastrow = Sheets("Source").Range("A65536").End(xlUp).Row
    
    For v_counter = 2 To v_lastrow
        v_expnum = Sheets("source").Range("A" & v_counter).Value
        
        v_date = Split(v_expnum, "_")
        
        If IsEmpty(v_expnum) Then
            v_success = "NO"
        
        ElseIf Not IsEmpty(v_expnum) And v_success = "YES" Then
        
            If Len(v_date(0)) = 8 Then
                ' assigning day,month and year to the 3 different variables
                v_year = Mid(v_expnum, 5, 4)
                v_month = Left(v_expnum, 2)
                v_day = Mid(v_expnum, 3, 2)
                
                v_SampleDate = v_date & "/" & v_month & "/" & v_year
                
                If IsDate(v_SampleDate) = False Then
                    v_success = "NO"
                    
                ElseIf DateValue(v_SampleDate) > Date Then
                    Sheets("Destination").Range("A" & v_counter).Value = DateSerial(v_year, v_month, v_day)
                    
                Else
                    v_success = "NO"
                    
                End If
                
            Else
                v_success = "NO"
        
            End If
        
        End If
        
    Next v_counter
    
End Sub
 
Upvote 0
Maybe this will work for you

Code: 
Option Explicit

Sub exp()
    Dim v_counter As Integer
    Dim v_expnum As String
    Dim v_lastrow As Integer
    Dim v_year As String
    Dim v_month As String
    Dim v_day As String
    Dim v_date As Date
    v_counter = 2
    v_lastrow = Sheets("Source").Range("A65536").End(xlUp).Row
    For v_counter = 2 To v_lastrow
        v_expnum = Sheets("source").Range("A" & v_counter).Value
        ' assigning day,month and year to the 3 different variables
        v_year = Mid(v_expnum, 5, 4)
        v_month = Left(v_expnum, 2)
        v_day = Mid(v_expnum, 3, 2)
        v_date = DateValue(v_month & "/" & v_day & "/" & v_year)
        If IsDate(v_date) And v_date <= Date Then
            Sheets("Destination").Range("A" & v_counter).Value = DateSerial(v_year, v_month, v_day)
        End If
    Next v_counter
End Sub
 
Upvote 0
Sorry,

Please replace below line:

"v_SampleDate = v_date & "/" & v_month & "/" & v_year" With "v_SampleDate = v_day & "/" & v_month & "/" & v_year"

And

"ElseIf DateValue(v_SampleDate) > Date Then" With "ElseIf DateValue(v_SampleDate) < Date Then"
 
Upvote 0
Hi Bill and Somnath,

I am getting Type mismatch error. I am unable to correct it. I would be greateful to you if you provide the correct syntax.

v_SampleDate = v_date & "/" & v_month & "/" & v_year (Hi Somnath I am getting type mismatch error at this line)

In Bill code also I am getting same error at (v_date assignment)

Thanks & Regards
Ramesh


<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 278.25pt; HEIGHT: 156pt" type="#_x0000_t75"><v:imagedata o:title="" src="file:///C:\DOCUME~1\R854C~1.KAT\LOCALS~1\Temp\msohtml1\01\clip_image001.png"></v:imagedata></v:shape>
 
Upvote 0
Please replace below line in my code:

"v_SampleDate = v_date & "/" & v_month & "/" & v_year" With "v_SampleDate = v_day & "/" & v_month & "/" & v_year"

And

"ElseIf DateValue(v_SampleDate) > Date Then" With "ElseIf DateValue(v_SampleDate) < Date Then"
 
Upvote 0
Hi Somnath,

I changed your code as you directed but I am getting type mismatch error. Please kindly have look into it and send to me the correct code.


Thanks & Regards
Ramesh
 
Upvote 0
Hello Ramesh,

Now try:


Code: 
Option Explicit

Sub exp()
    Dim v_counter As Integer
    Dim v_expnum As String
    Dim v_lastrow As Integer
    Dim v_date As Variant
    Dim v_success As String
    Dim v_SampleDate As String
    Dim v_year, v_month, v_day As String
    
    
    v_counter = 2
    v_success = "YES"
    v_lastrow = Sheets("Source").Range("A65536").End(xlUp).Row
    
    For v_counter = 2 To v_lastrow
        v_expnum = Sheets("source").Range("A" & v_counter).Value
        
        v_date = Split(v_expnum, "_")
        
        If IsEmpty(v_expnum) Then
            v_success = "NO"
        
        ElseIf Not IsEmpty(v_expnum) And v_success = "YES" Then
        
            If Len(v_date(0)) = 8 Then
                ' assigning day,month and year to the 3 different variables
                v_year = Mid(v_expnum, 5, 4)
                v_month = Left(v_expnum, 2)
                v_day = Mid(v_expnum, 3, 2)
                
                v_SampleDate = v_day & "/" & v_month & "/" & v_year
                
                If IsDate(v_SampleDate) = False Then
                    v_success = "NO"
                    
                ElseIf DateValue(v_SampleDate) < Date Then
                    Sheets("Destination").Range("A" & v_counter).Value = DateSerial(v_year, v_month, v_day)
                    
                Else
                    v_success = "NO"
                    
                End If
                
            Else
                v_success = "NO"
        
            End If
        
        End If
        
    Next v_counter
    
End Sub


Please insure the "v_SampleDate" declaration part
 
Upvote 0
Hi Somnath and forum members,

I have other 2 scenarious to check with this macro.

The scenarios are:

1. In exp. num the date part should not only date 07162009 instead of 07162009_n1

( if it is 07162007 the record should move to error sheet)


2. The date in the date part should not be 20070716_n1

( if it is like 20070716_n1 the record should move to error sheet)


How we check these two conditions. Please help me with the code.

Thanks
Ramesh
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
821
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
591
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
348
esben
E

Forum statistics

Threads
1,214,812
Messages
6,121,704
Members
449,048
Latest member
81jamesacct

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