date validation should not be > today()

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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

somnath_it2006

Well-known Member
Joined
Apr 11, 2009
Messages
574
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
 

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,412
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
 

somnath_it2006

Well-known Member
Joined
Apr 11, 2009
Messages
574
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"
 

ramserp

Board Regular
Joined
May 1, 2009
Messages
62

ADVERTISEMENT

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>
 

somnath_it2006

Well-known Member
Joined
Apr 11, 2009
Messages
574
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"
 

ramserp

Board Regular
Joined
May 1, 2009
Messages
62

ADVERTISEMENT

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
 

somnath_it2006

Well-known Member
Joined
Apr 11, 2009
Messages
574
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
 

ramserp

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

Thank you very much for you help. It is working now.


Thanks & Regards
Ramesh
 

ramserp

Board Regular
Joined
May 1, 2009
Messages
62
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,104
Messages
5,599,746
Members
414,333
Latest member
willfrederick

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
Top