UDF to return Date Components in an Array [VBA]

[drpdrpdrp]

I want to return the date components (Year, Month, DayOfYear, DayOfMonth) using a UDF.
Can someone explain why the code below won't work ?

Function DateYrMoDoyDom(InputDate As Range)
    'makes it easier to extract Year, Month, DayOfYear, DayOfMonth from date
    Dim i As Long, rows As Long, r
    r = InputDate
    rows = UBound(r)
    ReDim a(1 To rows, 1 To 4)
    For i = 1 To rows
        a(i, 1) = Year(r(i, 1))
        a(i, 2) = Month(r(i, 1))
        a(i, 3) = r(i, 1) - DateSerial(Year(r(i, 1)), 1, 0)
        a(i, 4) = Day(r(i, 1))
    Next i
    
    DateYrMoDoyDom = a
End Function

 

[drpdrpdrp]

As The input is a single cell use

Function DateYrMoDoyDom(InputDate As Range)
    'makes it easier to extract Year, Month, DayOfYear, DayOfMonth from date
    Dim i As Long, rows As Long, r
    r = InputDate
    ReDim A(1 To 1, 1 To 4)
        A(1, 1) = Year(r)
        A(1, 2) = Month(r)
        A(1, 3) = r - DateSerial(Year(r), 1, 0)
        A(1, 4) = Day(r)
   
   
    DateYrMoDoyDom = A
End Function

HAH! My code fails for an Array with ONE row, but it works as intended for multiple rows. Thanks Fluff and Rick.

 

[drpdrpdrp]

NOTE: I deleted my previous message as I developed a more compact solution

Like Fluff said, since the input is a single cell, here is another UDF that you can try...

Function DateYrMoDoyDom(DateIn As Range) As Variant
  DateYrMoDoyDom = Split(Format(DateIn, "yyyy mm y dd"))
End Function

Woah. Now that is ELEGANT. Thanks Rick

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[drpdrpdrp]

As The input is a single cell use

Function DateYrMoDoyDom(InputDate As Range)
    'makes it easier to extract Year, Month, DayOfYear, DayOfMonth from date
    Dim i As Long, rows As Long, r
    r = InputDate
    ReDim A(1 To 1, 1 To 4)
        A(1, 1) = Year(r)
        A(1, 2) = Month(r)
        A(1, 3) = r - DateSerial(Year(r), 1, 0)
        A(1, 4) = Day(r)
   
   
    DateYrMoDoyDom = A
End Function

Marking this as solution . Thanks Fluff

 

[drpdrpdrp]

When you say "won't work", what does that mean? Exactly what happens when you try to use your UDF?

#VALUE! error

 

[JoeMo]

NOTE: I deleted my previous message as I developed a more compact solution

Like Fluff said, since the input is a single cell, here is another UDF that you can try...

Function DateYrMoDoyDom(DateIn As Range) As Variant
  DateYrMoDoyDom = Split(Format(DateIn, "yyyy mm y dd"))
End Function

Another one-liner notch in the belt Rick!

If I use today's date 5/20/2022 in a cell and format the cell as "yyyy mm y dd" I see the value property of the date in the formula bar as 5/20/2022 and the text property of the date (in the cell) as 2022 05 22 20. How does the split function know that the 3rd element of the zero-based array it produces should be 140 (i.e. days in the year) and not 22 (the text value in the cell)?

If I format a cell as Text and enter 2022 05 22 20, so the cell and the formula bar match, your UDF returns an array with 22 as its 3rd element as expected.

 

[Rick Rothstein]

Another one-liner notch in the belt Rick!

If I use today's date 5/20/2022 in a cell and format the cell as "yyyy mm y dd" I see the value property of the date in the formula bar as 5/20/2022 and the text property of the date (in the cell) as 2022 05 22 20. How does the split function know that the 3rd element of the zero-based array it produces should be 140 (i.e. days in the year) and not 22 (the text value in the cell)?

If I format a cell as Text and enter 2022 05 22 20, so the cell and the formula bar match, your UDF returns an array with 22 as its 3rd element as expected.

Yeah, I guess I have managed to post a few one-liners across the years.

As for your question, the Split function always produces a zero-based array (no matter what the Option Base setting is), so the 3rd element is at index 2, not 3. As it turns out, Excel does not care what the indexing of an array is when array-entering its values horizontally into cells, it puts the first element in the first cell, the second element into the second cell and so on.

 

[JoeMo]

Yeah, I guess I have managed to post a few one-liners across the years.

As for your question, the Split function always produces a zero-based array (no matter what the Option Base setting is), so the 3rd element is at index 2, not 3. As it turns out, Excel does not care what the indexing of an array is when array-entering its values horizontally into cells, it puts the first element in the first cell, the second element into the second cell and so on.

I understand that and recognize that split always returns a zero-based string, but that doesn't answer my question. My question is why does split return the value 140 (which is the day of the year) rather than the 22 that the formatted date returns in the 3rd position?

 

[Rick Rothstein]

I understand that and recognize that split always returns a zero-based string, but that doesn't answer my question. My question is why does split return the value 140 (which is the day of the year) rather than the 22 that the formatted date returns in the 3rd position?

Ah, I see now (I misread your question earlier). It is not Split, per se, that is returning 140, rather, it is the Format function that is feeding the Split function. Format is a VBA function that has nothing to do with Excel's Cell Formatting (or is formula equivalent, the TEXT function). When processing dates, VBA's Format function returns the day of the year for a single "y" meta-character and the two-digit year for the "yy" meta-character pair. It is documented, see here...

Format function (Visual Basic for Applications)

Another interesting meta-character that I had forgotten about until I reread the help file is "c".

 

[JoeMo]

Ah, I see now (I misread your question earlier). It is not Split, per se, that is returning 140, rather, it is the Format function that is feeding the Split function. Format is a VBA function that has nothing to do with Excel's Cell Formatting (or is formula equivalent, the TEXT function). When processing dates, VBA's Format function returns the day of the year for a single "y" meta-character and the two-digit year for the "yy" meta-character pair. It is documented, see here...

Format function (Visual Basic for Applications)

Another interesting meta-character that I had forgotten about until I reread the help file is "c".

I was totally unaware that VBA's Format function returns the day of the year for a single "y" meta-character. Thanks for instilling some new knowledge in my ancient brain.