F. to count numbers of DOB as loose number

jabakka

New Member
Joined
Jun 7, 2019
Messages
6
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
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]
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
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]
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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}}),
    #"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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
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
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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.
 

Forum statistics

Threads
1,082,438
Messages
5,365,531
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top