Date function

vmjan02 · Oct 25, 2023

I have this code and not sure as what is wrong, it is not giving me the required result, any help

in row no 8 have this date

	11/01/23, 00:00	12/01/23, 00:00	13/01/23, 00:00	01/02/23, 00:00	02/02/23, 00:00	03/02/23, 00:00
Result	11/01/23	12/01/23	13/01/23	01/02/23	02/02/23	03/02/23
	11-Jan	12-Jan	13-Jan	1-Feb	2-Feb	3-Feb

VBA Code:

Sub TO_DATE1()
    Range("R2").Select
    Selection.Formula = "=IF(R8="""","""",IF(OR(R8=""NE_STATE"",R8=""Short name"",R8=""OLT""),"""",R8))"
    Selection.Copy
    Range("R2").Select
'    Range(Selection, Selection.End(xlToRight)).Select
    Range("R2:LZ2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

'On Error Resume Next

Dim e As Range, te As String
For Each e In Range("R2:LZ2")
    te = e.Value
    If Mid(te, 2, 1) = "/" Then te = "0" & te
    If Len(te) > 7 Then e = CDate(Left(te, 8))
Next
Range("R2:LZ2").NumberFormat = "dd/mm/yy"
End Sub

Herakles · Oct 25, 2023

vmjan02 said:
I have this code and not sure as what is wrong, it is not giving me the required result, any help

in row no 8 have this date

11/01/23, 00:00 12/01/23, 00:00 13/01/23, 00:00 01/02/23, 00:00 02/02/23, 00:00 03/02/23, 00:00
Result 11/01/23 12/01/23 13/01/23 01/02/23 02/02/23 03/02/23
11-Jan 12-Jan 13-Jan 1-Feb 2-Feb 3-Feb

VBA Code:

Sub TO_DATE1() Range("R2").Select Selection.Formula = "=IF(R8="""","""",IF(OR(R8=""NE_STATE"",R8=""Short name"",R8=""OLT""),"""",R8))" Selection.Copy Range("R2").Select ' Range(Selection, Selection.End(xlToRight)).Select Range("R2:LZ2").Select ActiveSheet.Paste Application.CutCopyMode = False 'On Error Resume Next Dim e As Range, te As String For Each e In Range("R2:LZ2") te = e.Value If Mid(te, 2, 1) = "/" Then te = "0" & te If Len(te) > 7 Then e = CDate(Left(te, 8)) Next Range("R2:LZ2").NumberFormat = "dd/mm/yy" End Sub

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

vmjan02 · Oct 25, 2023

here you go,

Nokia VLAN Traffic IN Daily -V1.xlsm

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

1

2

01/07/2023

01/08/2023

01/09/2023

01/10/2023

01/11/2023

01/12/2023

23/01/2013

3

4

5

6

7

8

07/01/23, 00:00

08/01/23, 00:00

09/01/23, 00:00

10/01/23, 00:00

11/01/23, 00:00

12/01/23, 00:00

13/01/23, 00:00

14/01/23, 00:00

15/01/23, 00:00

16/01/23, 00:00

17/01/23, 00:00

18/01/23, 00:00

19/01/23, 00:00

20/01/23, 00:00

9

3.06845E+12

2.90603E+12

3.30355E+12

2.83128E+12

2.83665E+12

3.00948E+12

3.0468E+12

3.16649E+12

3.16175E+12

3.04794E+12

2.82622E+12

3.01735E+12

3.12863E+12

3.24451E+12

10

2.02025E+13

1.80057E+13

1.97399E+13

1.78594E+13

1.9489E+13

1.84219E+13

2.01856E+13

2.24439E+13

2.14328E+13

1.94547E+13

1.93774E+13

1.96367E+13

2.00135E+13

2.04886E+13

11

5.37341E+13

5.04525E+13

5.03153E+13

4.88383E+13

4.89635E+13

4.9587E+13

6.1265E+13

5.26875E+13

5.20574E+13

5.05483E+13

4.86879E+13

5.00431E+13

5.06816E+13

6.05485E+13

12

5.67331E+13

4.9213E+13

5.46853E+13

4.77041E+13

5.0679E+13

5.0201E+13

5.4412E+13

5.56879E+13

5.33331E+13

5.30923E+13

5.05004E+13

5.53005E+13

5.27154E+13

5.55282E+13

13

5.04924E+13

4.96107E+13

5.38011E+13

4.92973E+13

4.57306E+13

5.1556E+13

5.09729E+13

5.05169E+13

4.79752E+13

4.67954E+13

4.56062E+13

5.09003E+13

5.20419E+13

5.47822E+13

14

5.82176E+13

5.7111E+13

6.23844E+13

5.52229E+13

5.2813E+13

6.1053E+13

5.89999E+13

6.00321E+13

5.65849E+13

5.35906E+13

4.93561E+13

5.70421E+13

6.16078E+13

6.41756E+13

15

3.60144E+13

3.60809E+13

3.89735E+13

3.4635E+13

3.34112E+13

3.73852E+13

3.50358E+13

3.67278E+13

3.57901E+13

3.45482E+13

3.24343E+13

3.67637E+13

3.80834E+13

3.78083E+13

16

4.17636E+13

4.05771E+13

4.44509E+13

3.96323E+13

3.68347E+13

4.22796E+13

3.95644E+13

4.05234E+13

3.94942E+13

3.94978E+13

3.74776E+13

4.03046E+13

4.21159E+13

4.36659E+13

17

2.34576E+13

2.32089E+13

2.20241E+13

2.32954E+13

2.18373E+13

2.36615E+13

2.30809E+13

2.29219E+13

2.19041E+13

2.12124E+13

2.1561E+13

2.36366E+13

2.48126E+13

2.65468E+13

18

3.50704E+13

3.4919E+13

3.51274E+13

3.65808E+13

3.40239E+13

3.85869E+13

3.6565E+13

3.80887E+13

3.6803E+13

3.65089E+13

3.36128E+13

3.71624E+13

3.90888E+13

3.89198E+13

19

4.67637E+13

4.1764E+13

4.65392E+13

3.99217E+13

4.44107E+13

4.39842E+13

4.7237E+13

4.71981E+13

4.51594E+13

4.44681E+13

4.13172E+13

4.48871E+13

4.48841E+13

4.69083E+13

20

1.92632E+13

1.85455E+13

2.03177E+13

1.92304E+13

1.78454E+13

1.99801E+13

2.02395E+13

2.18437E+13

1.88662E+13

1.88191E+13

1.78905E+13

1.88314E+13

1.99079E+13

2.11501E+13

21

3.03128E+13

2.79463E+13

3.08E+13

2.69527E+13

2.69774E+13

2.87943E+13

3.05914E+13

2.96563E+13

3.05846E+13

2.8843E+13

2.65272E+13

2.89355E+13

2.89236E+13

3.16101E+13

22

2.21228E+13

1.8983E+13

1.96145E+13

1.76097E+13

2.03697E+13

1.96372E+13

2.0931E+13

2.3998E+13

2.32343E+13

2.08789E+13

2.11284E+13

1.98015E+13

2.07178E+13

2.23969E+13

23

1.8098E+13

1.76274E+13

1.94947E+13

1.82121E+13

1.75575E+13

1.90946E+13

1.89762E+13

1.86959E+13

1.7757E+13

1.67704E+13

1.65241E+13

1.79115E+13

1.79145E+13

1.96492E+13

Sheet1

Cell Formulas
Range	Formula
X1:AK1	X1	=IF(X8="","",IF(OR(X8="NE_STATE",X8="Short name",X8="OLT"),"",X8))
AE2:AK2	AE2	=IF(AE8="","",IF(OR(AE8="NE_STATE",AE8="Short name",AE8="OLT"),"",AE8))

vmjan02 · Oct 26, 2023

hi any update on this please

Joe4 · Oct 26, 2023

How about an exaplanation (in plain English) of exactly what it is that you are trying to do?
It is not obvious to me (and I suspect to others as well).

vmjan02 · Oct 26, 2023

Joe4 said:
How about an exaplanation (in plain English) of exactly what it is that you are trying to do?
It is not obvious to me (and I suspect to others as well).

I am trying to get the date in row 2 from row 8, using the script, but after executin the script its not giving me the correct resulit in row 2
the acutal date is dd/mm/yyyy, 00:00 in row 8
out put in row 2 sould be dd/mm/yyyy

I am sure now its clear, i have kept the table in my first post and sample data in my 2 post.

Joe4 · Oct 26, 2023

I am assuming that your values in row 8 are entered as Text and not as Date (that is what the left-justification seems to indicate).

If that is so, then try this formula for cell X2:

Excel Formula:

=IF(X8="","",IF(OR(X8="NE_STATE",X8="Short name",X8="OLT"),"",DATE("20" & MID(X8,7,2),MID(X8,4,2),LEFT(X8,2))))

and be sure to format row to the custom date format of "dd/mm/yyyy".

Date function

vmjan02

Well-known Member

Excel Facts

Herakles

Well-known Member

vmjan02

Well-known Member

vmjan02

Well-known Member

Joe4

MrExcel MVP, Junior Admin

vmjan02

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

Date function

vmjan02

Well-known Member

Excel Facts

Herakles

Well-known Member

vmjan02

Well-known Member

vmjan02

Well-known Member

Joe4

MrExcel MVP, Junior Admin

vmjan02

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock