F. to count numbers of DOB as loose number

[jabakka]

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?

Many thanks in advance

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

 

[lrobbo314]

If you have the DOB in cell A1, then this formula seems to work.

=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.

 

[lrobbo314]

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

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

 

[Rick Rothstein]

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

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...

[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]

 

[Rick Rothstein]

For those who might be interested, here is a way to write the above function without using loops...

[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...

[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]

 

[lrobbo314]

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.

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DOB", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.ToList(Text.Replace(Text.From([DOB]),"/",""))),
    #"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}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.ToList(Text.From([Sum1]))),
    #"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}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows1", "LIFEDAYS", each Text.From([Sum1])&"/"&Text.From([Sum2])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Sum1", "Sum2"})
in
    #"Removed Columns"

 

[Rick Rothstein]

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

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...

[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...

[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...

[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]

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.

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

 

[Rick Rothstein]

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.

 

[lrobbo314]

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

Either way, this

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.