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]

Updated Power Query solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DOB", type date}}),
    LeftSide = Table.AddColumn(#"Changed Type", "pt1", each List.Accumulate(Text.ToList(Text.Replace(Text.From([DOB]),"/","")),0,(state,current)=>state + Number.From(current))),
    RightSide = Table.AddColumn(LeftSide, "pt2", each List.Accumulate(Text.ToList(Text.From([pt1])),0,(state,current)=>state + Number.From(current))),
    Merge = Table.CombineColumns(Table.TransformColumnTypes(RightSide, {{"pt1", type text}, {"pt2", type text}}, "en-US"),{"pt1", "pt2"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged")
in
    Merge

 

[sandy666]

Updated Power Query solution

Nice job

 

[lrobbo314]

Nice job

Thanks buddy! Your PQ posts definitely make me look for better solutions.

 

[jabakka]

Thanks for all the answers.

Since I am not that advanced in excel and because I can't get any of the examples to work, I want to use the simplest array formula given in your reply.

I copied a DOB to A1 as revered to, in the given formula and pasted your array formula to B1. I'm getting "Err:501".
I used the Ctrl+Shift+Enter method for array formulas as well as Insert>functions>formula and pressing the array function (using Librecalc but saving as .xls format).

It does not seem to work. Is there something I missed?
See this dropbox link for the excel file

 

[sandy666]

If you'll use MS Excel with xlsx extension not LibreOffice formula will work

 

[jabakka]

If you'll use MS Excel with xlsx extension not LibreOffice formula will work

I did that also.. No luck.

 

[sandy666]

what is your Excel version?

I can't check for less than 2016 but it should work for higher than 2007
Excel File

 

[jabakka]

what is your Excel version?

I can't check for less than 2016 but it should work for higher than 2007
Excel File

Yes that works perfectly!
And the formula also works in OSD file format! (I am using libre office/calc 6.1.3.2)


Thank you very much.

 

[sandy666]

I asked about Excel version not LibreOffice but if it works for you that's ok

Have a nice day

 

[Marcelo Branco]

A possible solution using built-in functions


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Date​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

01/01/1998​

[/td][td]

29/11​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

12/01/1998​

[/td][td]

31/4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

01/01/2003​

[/td][td]

7/7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

01/01/1900​

[/td][td]

12/3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

12/12/2020​

[/td][td]

10/1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

14/06/1984​

[/td][td]

33/6​

[/td][/tr]
[/table]

Formula in B2 copied down
=SUMPRODUCT(--(MID(TEXT(A2,"ddmmyyyy"),ROW(A$1:A$8),1)))&"/"&SUMPRODUCT(--("0"&MID(SUMPRODUCT(--(MID(TEXT(A2,"ddmmyyyy"),ROW(A$1:A$8),1))),ROW(A$1:A$8),1)))

M.