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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

ramserp

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

I have given wrong sheet name to you. If it is wrong date it is not move to destination. It should be in the same sheet.


Thanks
Ramesh
 

somnath_it2006

Well-known Member
Joined
Apr 11, 2009
Messages
574
Hello Ramesh,

Could you please conform below statement:

Code:
ElseIf Not IsEmpty(v_expnum) And v_success = "YES" Then

Why you have checked v_success = "YES".
Because according to this, if loop found any wrong date at any state it will not process next dates.
 

somnath_it2006

Well-known Member
Joined
Apr 11, 2009
Messages
574
If that statement is OK then... below is the code in which your both conditions checked.


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) Or UBound(v_date) < 1 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
 

ramserp

Board Regular
Joined
May 1, 2009
Messages
62

ADVERTISEMENT

Hello Somnath,

I declared v_success variable for flag. If the condition is true then v_success="NO" else v_success="YES".

When v_success="YES" the move to the destination sheet but I didn't used v_success="YES" method to store values.


Thanks
Ramesh
 

somnath_it2006

Well-known Member
Joined
Apr 11, 2009
Messages
574
Ok.. So without flag you can apply proper logic.

Check the below code with comments:

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
    Dim DestStatus As Long
    
    
    
    
    ' Get last row number
    v_lastrow = Sheets("Source").Range("A65536").End(xlUp).Row
    
    ' Counter for destination sheet data status
    DestStatus = 2
    
    ' Loop from 2 to last data row of sheet
    For v_counter = 2 To v_lastrow
        
        ' Get date from cell
        v_expnum = Sheets("source").Range("A" & v_counter).Value
        
        ' Split date by "_"
        v_date = Split(v_expnum, "_")
        
        ' If date cell is empty or after split there is no value found in
        ' Second index of array (means there is no "_" found) then goto next value
        If IsEmpty(v_expnum) Or UBound(v_date) < 1 Then
            GoTo NextV_Counter
        
        Else
            
            ' If first part of index is like date (8 digit) 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)
                   
                ' Prepare propar date string
                v_SampleDate = v_day & "/" & v_month & "/" & v_year
                
                ' Check string is date or not
                If IsDate(v_SampleDate) = False Then
                    GoTo NextV_Counter
                
                ' If date is less than todays date then only move to destination sheet
                ElseIf DateValue(v_SampleDate) < Date Then
                    Sheets("Destination").Range("A" & DestStatus).Value = DateSerial(v_year, v_month, v_day)

                    ' Increase the row counter
                    DestStatus = DestStatus + 1
                    
                Else
                    GoTo NextV_Counter
                End If
                
            Else
                GoTo NextV_Counter
        
            End If
        
        End If
        
' Jump to next cell counter
NextV_Counter:
        

    Next v_counter
    
End Sub


Revert in case of other queries.
 

ramserp

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

The logic implemented by you is good.

Thank you very much for spending your valuable time for giving reply to my posts.


Thanks & Regards
Ramesh
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

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