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.

 

[Rick Rothstein]

...I want to use the simplest array formula given in your reply.

Here is a normally-entered formula that should do what you want (your date is assumed to be in cell A1)...

[table="width: 500"]
[tr]
	[td]=SUMPRODUCT(0+MID(TEXT(A1,"mmddyyyy"),ROW(1:8),1))&"/"&SUM(0+MID(TEXT(SUMPRODUCT(0+MID(TEXT(A1,"mmddyyyy"),ROW(1:8),1)),"00"),{1,2},1))[/td]
[/tr]
[/table]

 

[Marcelo Branco]

I think your code fails if the digits in the date total less than 10 (for example, 1/2/2003)

One more simplification for the above code...

[TABLE="width: 500"]
<tbody>[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]
</tbody>[/TABLE]

@Rick Rothstein

Hi Rick,

Using this function I am getting a strange result for 01/01/1900, i.e., 34/7
I'm not sure if this is because my Regional Setting for dates is dd/mm/yyyy

M.

 

[Rick Rothstein]

@Rick Rothstein
Hi Rick,

Using this function I am getting a strange result for 01/01/1900, i.e., 34/7
I'm not sure if this is because my Regional Setting for dates is dd/mm/yyyy

I think the problem is because Excel mistakenly thinks 1900 was a Leap Year when, in fact, it was not... and VB knows it was not. Apparently, VB is "correcting" for this and bringing Excel date 1/1/1900 into the VB world as the day before 12/31/1899 whose numbers add up to 34/7. It looks like this problem (use of previous day) will exist for any date prior to March 1st, 1900. Let me think on this for a bit to see if I can come up with a "fix".

 

[Marcelo Branco]

I think the problem is because Excel mistakenly thinks 1900 was a Leap Year when, in fact, it was not... and VB knows it was not. Apparently, VB is "correcting" for this and bringing Excel date 1/1/1900 into the VB world as the day before 12/31/1899 whose numbers add up to 34/7. It looks like this problem (use of previous day) will exist for any date prior to March 1st, 1900. Let me think on this for a bit to see if I can come up with a "fix".

Interesting point - I would never have imagined that this could be the cause. Good catch!

M.

 

[Rick Rothstein]

Here is a normally-entered formula that should do what you want (your date is assumed to be in cell A1)...

[table="width: 500"]
[tr]
	[td]=SUMPRODUCT(0+MID(TEXT(A1,"mmddyyyy"),ROW([B][COLOR="#FF0000"]$[/COLOR][/B]1:[B][COLOR="#FF0000"]$[/COLOR][/B]8),1))&"/"&SUM(0+MID(TEXT(SUMPRODUCT(0+MID(TEXT(A1,"mmddyyyy"),ROW([B][COLOR="#FF0000"]$[/COLOR][/B]1:[B][COLOR="#FF0000"]$[/COLOR][/B]8),1)),"00"),{1,2},1))[/td]
[/tr]
[/table]

Minor fix needed for the above so that it can be copied down successfully. The formula needs the $ signs I show in red above.

 

[jabakka]

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

Have a nice day

I do not own Excel or Microsoft office. All libre office now.

 

[sandy666]

I do not own Excel or Microsoft office. All libre office now.

so next time use this forum: General Excel Discussion & Other Questions This forum is for Excel-related discussion and questions concerning programs other than Excel or Access. This is also the place to ask about book suggestions or other websites.
don't forget to say in the first post something about your software, eg. LibreOffice user

 

[jabakka]

so next time use this forum: General Excel Discussion & Other Questions This forum is for Excel-related discussion and questions concerning programs other than Excel or Access. This is also the place to ask about book suggestions or other websites.
don't forget to say in the first post something about your software, eg. LibreOffice user

Thanks for the advice. I will do that.