how to reduce a date TO A SINGLE DIGIT

[zebra7860]

Hi,

I have dates in the following format ( MM/DD/YYYY) starting with cell D2:

ie 5/15/1997
ie 01/22/2018

I simply want to create a mod formula in cell E2 that summarizes the date value to a single digit, ie:

5+1+5+1+9+9+7 = 37 = 10 = 1+0 = 1
1+2+2+2+0+1+8 = 34 = 7

Can anybody help?

Thanks,
Al

 

[Fluff]

Are your dates real dates, or text?

 

[zebra7860]

real dates, ie:

10/13/1988

06/19/2014

7/25/2018

09/30/1994

10/27/1997

02/04/2013

 

[JamesCanale]

inelegant:

=INT((INT(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1)))/10)+MOD(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1))),10))/10)+MOD((INT(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1)))/10)+MOD(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1))),10)),10)

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B
4	22/01/2018	7
5	13/10/1988	4
6	19/06/2014	5
7	25/07/2018	7
8	30/09/1994	8
9	27/10/1997	9
10	04/02/2013	3
Main

Cell Formulas
Range	Formula
B4:B10	B4	=1+(TEXT(A4,"ddmmyyyy")-1)-(INT((TEXT(A4,"ddmmyyyy")-1)/9)*9)

 

[zebra7860]

Hi,
Are you sure we can't have a MOD function? This is too long and when I used it by for date 05/15/1997 I get "0"
And I changed the 'p2' to D2 to reflect the date cell

Thanks for the offer

 

[JamesCanale]

I get a 1 when I do that. But use Fluff's. It is much simpler to use.

MrExcelPlayground2.xlsx
	P	Q	R	S	T	U	V	W
2	5/15/1997	05	15	1997	05151997	37	10	1
Sheet26

Cell Formulas
Range	Formula
Q2	Q2	=TEXT(MONTH(P2),"00")
R2	R2	=TEXT(DAY(P2),"00")
S2	S2	=TEXT(YEAR(P2),"0000")
T2	T2	=TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")
U2	U2	=SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1)))
V2	V2	=INT(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1)))/10)+MOD(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1))),10)
W2	W2	=INT((INT(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1)))/10)+MOD(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1))),10))/10)+MOD((INT(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1)))/10)+MOD(SUM(VALUE(MID(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000"),ROW(INDIRECT("1:"&LEN(TEXT(MONTH(P2),"00")&TEXT(DAY(P2),"00")&TEXT(YEAR(P2),"0000")))),1))),10)),10)

 

[Fluff]

As you are just dealing with dates, you should be able to just use

=1+(MOD(TEXT(A4,"ddmmyyyy")-1,9))

 

[zebra7860]

Hi Fluff,
I revamped your code to =1+(TEXT(D2,"mmddyyyy")-1)-(INT((TEXT(D2,"mmddyyyy")-1)/9)*9)
This reflects the fact that the beginning date is in cell D2, and I changed the ddmmyyyy to mmddyyyy but I still get " #VALUE!

Any thoughts?

 

[Fluff]

It doesn't matter what date format you use as you are just looking at the individual digits, so the order is irrelevant.
What does this return

=ISTEXT(D2)