Extract Date from Last Number

[muhammad susanto]

hi all..
how to extract or pull number from last date number
note use vba/macro

Book1
	F	G
2	date (dd-mm-yyyy)	extract number
3	23/10/2020	0
4	09/12/2016	6
5	01/01/2017	7
6	23/10/1995	5
Sheet1

thank in advance
.sst

 

[Fluff]

Are you saying you want a macro for this, rather than a simple formula?

 

[Saurabhj]

Hi, using formula:

Queries-2.xlsm
	A	B
1	date (dd-mm-yyyy)	extract number
2	23-10-2020	0
3	09-12-2016	6
4	01-01-2017	7
5	23-10-1995	5
6
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=RIGHT(YEAR(A2),1)

 

[Saurabhj]

Using Macro:

Sub showLastDigit()
   With Sheets("Sheet1")
    For rowno = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(rowno, 3) = Right(Year(.Cells(rowno, 1)), 1)
    Next
End With
End Sub

 

[Fluff]

Another option without the loop

Sub muhammad()
   With Range("G3:G" & Range("F" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate("if({1},right(year(" & .Offset(, -1).Address & ")))")
   End With
End Sub

 

[muhammad susanto]

hi Saurabhj & Fluff ...thank you very much..
all that worked great!!
sorry i missing it :
note use macro should be don't use...

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Saurabhj]

Thanks for the feedback, Muhammad.