VBA macro not splitting the path name properly

[dpteo]

I have a code to split the path name into separate columns. However it is not splitting it correctly.

The S/N column is from the path after Gantry1\
The year column should be 2021
The month column should be 01
the date column should be 08

However after running the macro, the year became 1 and the month became 8 and the date became 16
I would greatly appreciate your help to find out what is the problem with my code.

        If InStr(DestinationRange, "Gantry1") > 0 Then
        
            DestinationRange.Offset(0, 3) = Split(Split(DestinationRange.Value, "Gantry1\")(1), "\")(0)  'S/N
            DestinationRange.Offset(0, 4) = Split(Split(DestinationRange.Value, "Gantry1\")(1), "_")(1)  'Year
            DestinationRange.Offset(0, 5) = Split(Split(DestinationRange.Value, "Gantry1\")(1), "_")(2)  'Month
            DestinationRange.Offset(0, 6) = Split(Split(DestinationRange.Value, "Gantry1\")(1), "_")(3)  'Day
            
            yearonly = Split(Split(DestinationRange.Value, "Gantry1\")(1), "_")(1)
            monthonly = Split(Split(DestinationRange.Value, "Gantry1\")(1), "_")(2)
            dayonly = Split(Split(DestinationRange.Value, "Gantry1\")(1), "_")(3)
            
            Dateonly = dayonly & "/" & monthonly & "/" & yearonly
            
            DestinationRange.Offset(0, 7) = Dateonly
            
            DestinationRange.Offset(0, 8) = WeekNum(Dateonly)

 

[rollis13]

If I correctly understood since I don't want to manually create a sample of your path:

'with this:
destinationrange.Offset(0, 3) = Split(destinationrange.Value, "Gantry1\")(1)
'you fetch:
ATM-MD-00002\2021_08_01_16_14_12\Report.pdf
'so to fetch the date from this these need to be:
destinationrange.Offset(0, 3) = Split(Split(destinationrange.Value, "Gantry1\")(1), "\")(0) 'S/N
destinationrange.Offset(0, 4) = Split(Split(Split(destinationrange.Value, "Gantry1\")(1), "\")(1), "_")(0) 'Year
destinationrange.Offset(0, 5) = Split(Split(Split(destinationrange.Value, "Gantry1\")(1), "\")(1), "_")(1) 'Month
destinationrange.Offset(0, 6) = Split(Split(Split(destinationrange.Value, "Gantry1\")(1), "\")(1), "_")(2) 'Day

then you can update your macro to rebuild your "Dateonly".

 

[GWteB]

Hi & welcome to MrExcel.

You're letting us know that the leading zeros of day and month should be preserved.
In that regard, in addition to @rollis13's contribution, I would like to note that it's necessary to format the respective columns as text.

 

[dpteo]

If I correctly understood since I don't want to manually create a sample of your path:

'with this:
destinationrange.Offset(0, 3) = Split(destinationrange.Value, "Gantry1\")(1)
'you fetch:
ATM-MD-00002\2021_08_01_16_14_12\Report.pdf
'so to fetch the date from this these need to be:
destinationrange.Offset(0, 3) = Split(Split(destinationrange.Value, "Gantry1\")(1), "\")(0) 'S/N
destinationrange.Offset(0, 4) = Split(Split(Split(destinationrange.Value, "Gantry1\")(1), "\")(1), "_")(0) 'Year
destinationrange.Offset(0, 5) = Split(Split(Split(destinationrange.Value, "Gantry1\")(1), "\")(1), "_")(1) 'Month
destinationrange.Offset(0, 6) = Split(Split(Split(destinationrange.Value, "Gantry1\")(1), "\")(1), "_")(2) 'Day

then you can update your macro to rebuild your "Dateonly".

Hi @rollis13, thanks for the help. It solved my problem!

 

[rollis13]

Glad having been of some help.