CODEWITHSUNDEEP
excelexcel-formula
Pritam
Pritam

Reputation: 1

Adding numbers of date to single digit

I want to add numbers in a date to a single digit number. For example, 28-July-1974 So if I add it as 2+8+7+1+9+7+4 = 38 as two digits total. I need it as 2 (3+8). What formula will help?

Upvotes: 0

Views: 729

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Consider the following User Defined Function:

Public Function numerify(rng As Range) As Integer
    Dim s As String, L As Long, i As Long, zum As Long
    Dim inpt As String
    
    inpt = rng.Text
    arr = Split(inpt, "-")
    mnth = Month(DateValue(arr(1) & "/1/2000"))
    s = arr(0) & CStr(mnth) & arr(2)
    
    While Len(s) > 1
        MsgBox s
        L = Len(s)
        zum = 0
        For i = 1 To L
            zum = zum + CLng(Mid(s, i, 1))
        Next i
        s = CStr(zum)
    Wend
    
    numerify = CInt(s)
End Function

It will repeated add digits until a single digit is achieved:

enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

You can use the MOD of 9 to get the sum to the single digit:

=MOD(--TEXT(A1,"ddmmyyyy")-1,9)+1

enter image description here

Upvotes: 4

Related Questions