Summing the digits of a date

[MatrixFX]

Hi, need help with a formula that will sum each digit of a date. For example, 9/22/2020, need a formula that will do the following...9+2+2+2+2=17

Here is the formula i tried, but it does not work on dates. =SUMPRODUCT(1*MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))


Any help will be greatly appreciated.

BJW

[bebo021999]

One way:

Please Login or Register  to view this content.

[bebo021999]

Alternative:

Please Login or Register  to view this content.

[MatrixFX]

Thanks for you help, but I am a little lost. I tried to copy and paste what you provided, starting with the = sign, but it did not work. Can you clarify pls.

[MatrixFX]

Beboo, thank you for your help. The formula is working perfectly!

[bluesky63]

Hi MatrixFX

A2 = 9/22/2020

=SUM(VALUE(MID(VALUE(SUBSTITUTE(A2,"/",0)),ROW(A1:OFFSET(A1,LEN(VALUE(SUBSTITUTE(A2,"/",0)))-1,0)),1)))

[maniacb]

in VBA

Please Login or Register  to view this content.