# F. to count numbers of DOB as loose number

#### jabakka

##### New Member
Hi Mr. Excellers,

I am working on a personal project where I am making a excel sheet, where I want to calculate Life numbers from the Day of birth of persons, based on the book of Dann Millman "The life your where born to live".

It goes as follow; you take a DOB and you count the loose numbers as separates.
so the person with the DOB 01-01-1998 will be: 0+1+0+1+1+9+9+8 = 29
and after that the 29 will be counted as 2+9= 11
Than you put them next to each other 29/11

in other words: Is it possible to use a formula to count all the numbers of a DOB as separate and show the outcome?

PS: sorry for my bad english, it's not my native language.

#### lrobbo314

##### Well-known Member
If you have the DOB in cell A1, then this formula seems to work.
Code:
``````=SUM(MID(TEXT(A1,"mmddyyyy"),ROW(INDIRECT("1:"&LEN(TEXT(A1,"mmddyyyy")))),1)+0)
&"/"&
SUM(MID(SUM(MID(TEXT(A1,"mmddyyyy"),ROW(INDIRECT("1:"&LEN(TEXT(A1,"mmddyyyy")))),1)+0),ROW(INDIRECT("1:"&LEN(SUM(MID(TEXT(A1,"mmddyyyy"),ROW(INDIRECT("1:"&LEN(TEXT(A1,"mmddyyyy")))),1)+0)))),1)+0)``````
This is an array formula so use Ctrl+Shift+Enter.

• jabakka

#### lrobbo314

##### Well-known Member
Although, that's a pretty ugly formula. I would prefer using a UDF.

Code:
``````Function LIFEDAYS(v As Variant)
Dim dt As String: dt = Format(v, "mmddyyyy")
Dim pt1 As Integer
Dim pt2 As Integer

For i = 1 To Len(dt)
pt1 = pt1 + Mid(dt, i, 1)
Next i

For j = 1 To Len(pt1)
pt2 = pt2 + Mid(pt1, j, 1)
Next j

LIFEDAYS = pt1 & "/" & pt2
End Function``````

• jabakka

#### Rick Rothstein

##### MrExcel MVP
Although, that's a pretty ugly formula. I would prefer using a UDF.

Code:
``````Function LIFEDAYS(v As Variant)
Dim dt As String: dt = Format(v, "mmddyyyy")
Dim pt1 As Integer
Dim pt2 As Integer

For i = 1 To Len(dt)
pt1 = pt1 + Mid(dt, i, 1)
Next i

For j = 1 To Len(pt1)
pt2 = pt2 + Mid(pt1, j, 1)
Next j

LIFEDAYS = pt1 & "/" & pt2
End Function``````
For those who might be interested, here is a way to write the above function without using loops...
Code:
``````[table="width: 500"]
[tr]
[td]Function LIFEDAYS(D As Date) As String
LIFEDAYS = Evaluate(Replace(StrConv(Evaluate(Format(D, "mmddyyyy")), vbUnicode), Chr(0), "+") & 0)
LIFEDAYS = LIFEDAYS & "/" & Evaluate(Replace(StrConv(LIFEDAYS, vbUnicode), Chr(0), "+") & 0)
End Function[/td]
[/tr]
[/table]``````

• jabakka

#### Rick Rothstein

##### MrExcel MVP
For those who might be interested, here is a way to write the above function without using loops...
Code:
``````[table="width: 500"]
[tr]
[td]Function LIFEDAYS(D As Date) As String
LIFEDAYS = Evaluate(Replace(StrConv(Evaluate(Format(D, "mmddyyyy")), vbUnicode), Chr(0), "+") & 0)
LIFEDAYS = LIFEDAYS & "/" & Evaluate(Replace(StrConv(LIFEDAYS, vbUnicode), Chr(0), "+") & 0)
End Function[/td]
[/tr]
[/table]``````
The above code can be simplified slightly by recognizing that the number to the left will always be either a single digit or a double digit...
Code:
``````[table="width: 500"]
[tr]
[td]Function LIFEDAYS(D As Date) As String
LIFEDAYS = Evaluate(Replace(StrConv(Evaluate(Format(D, "mmddyyyy")), vbUnicode), Chr(0), "+") & 0)
LIFEDAYS = LIFEDAYS & "/" & Evaluate(Format(LIFEDAYS, "0+0"))
End Function[/td]
[/tr]
[/table]``````

• jabakka

#### lrobbo314

##### Well-known Member
And, for grins, a Power Query method. Although, I'd like to see @sandy666 chime in on this because I'm sure he could streamline this.

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DOB", type date}}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"DOB"}, {{"Sum1", each List.Sum([Custom]), type text}}),
#"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Custom", Int64.Type}}),
#"Grouped Rows1" = Table.Group(#"Changed Type2", {"DOB", "Sum1"}, {{"Sum2", each List.Sum([Custom]), type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Sum1", "Sum2"})
in
#"Removed Columns"``````

• jabakka

#### Rick Rothstein

##### MrExcel MVP
Although, that's a pretty ugly formula. I would prefer using a UDF.

Code:
``````Function LIFEDAYS(v As Variant)
Dim dt As String: dt = Format(v, "mmddyyyy")
Dim pt1 As Integer
Dim pt2 As Integer

For i = 1 To Len(dt)
pt1 = pt1 + Mid(dt, i, 1)
Next i

For j = 1 To Len(pt1)
pt2 = pt2 + Mid(pt1, j, 1)
Next j

LIFEDAYS = pt1 & "/" & pt2
End Function``````
I think your code fails if the digits in the date total less than 10 (for example, 1/2/2003)

For those who might be interested, here is a way to write the above function without using loops...
Code:
``````[table="width: 500"]
[tr]
[td]Function LIFEDAYS(D As Date) As String
LIFEDAYS = Evaluate(Replace(StrConv(Evaluate(Format(D, "mmddyyyy")), vbUnicode), Chr(0), "+") & 0)
LIFEDAYS = LIFEDAYS & "/" & Evaluate(Replace(StrConv(LIFEDAYS, vbUnicode), Chr(0), "+") & 0)
End Function[/td]
[/tr]
[/table]``````
The above code can be simplified slightly by recognizing that the number to the left will always be either a single digit or a double digit...
Code:
``````[table="width: 500"]
[tr]
[td]Function LIFEDAYS(D As Date) As String
LIFEDAYS = Evaluate(Replace(StrConv(Evaluate(Format(D, "mmddyyyy")), vbUnicode), Chr(0), "+") & 0)
LIFEDAYS = LIFEDAYS & "/" & Evaluate(Format(LIFEDAYS, "0+0"))
End Function[/td]
[/tr]
[/table]``````
One more simplification for the above code...
Code:
``````[table="width: 500"]
[tr]
[td]Function LIFEDAYS(D As Date) As Variant
LIFEDAYS = Evaluate(Format\$(Replace(Format\$(D, "mdyyyy"), "/", ""), "@+@+@+@+@+@+@+@"))
LIFEDAYS = LIFEDAYS & "/" & Evaluate(Format(LIFEDAYS, "0+0"))
End Function[/td]
[/tr]
[/table]``````

#### lrobbo314

##### Well-known Member
Good call Rick. My initial thought was to convert pt1 to a string, but it worked with the date in the OP.

I didn't know that you could set 'LIFEDAYS=' twice the way that you did in your solutions.

I would use Rick's VBA. But, here is an updated version of mine that doesn't break.

Code:
``````Function LIFEDAYS(v As Variant)
Dim dt As String: dt = Format(v, "mmddyyyy")
Dim pt1 As Integer
Dim pt2 As Integer
Dim nTs As String

For i = 1 To Len(dt)
pt1 = pt1 + Mid(dt, i, 1)
Next i

nTs = pt1

For j = 1 To Len(nTs)
pt2 = pt2 + Mid(nTs, j, 1)
Next j

LIFEDAYS = pt1 & "/" & pt2
End Function``````

• jabakka

#### Rick Rothstein

##### MrExcel MVP
I didn't know that you could set 'LIFEDAYS=' twice the way that you did in your solutions.
While I am not 100% sure of the underlying mechanics, my thinking (and the code seems to bear it out) is that a function name is no different from a variable name... they both represent memory locations where values will be stored which means a function name acts like a variable name and, as long as it is a simple data type, can be used within the code to store intermediate results.

• jabakka

#### lrobbo314

##### Well-known Member
That's what it seems like, for sure. I'll have to keep that in mind for the future.

Either way, this
Code:
``````Function LIFEDAYS(D As Date) As Variant
LIFEDAYS = Evaluate(Format\$(Replace(Format\$(D, "mdyyyy"), "/", ""), "@+@+@+@+@+@+@+@"))
LIFEDAYS = LIFEDAYS & "/" & Evaluate(Format(LIFEDAYS, "0+0"))
End Function``````
... is a slick solution.

• jabakka