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

 

[zebra7860]

Fly

=ISTEXT(D2)

TRUE

 

[zebra7860]

Fluff,
I get a value of 2 when I use your code, but for 05/15/1997 it should be 1

 

[Fluff]

If you get True for =Istext then your dates are text & not numbers try

=1+(MOD(SUBSTITUTE(D2,"/","")-1,9))

 

[zebra7860]

Bingo!! That worked like a charm. Thank you, Fluff

 

[Dave Patton]

Date and Time 2021.xlsm
	A	B
1	5/15/1997	1
2	1/22/2018	7
10b

Cell Formulas
Range	Formula
B1:B2	B1	=MOD(SUBSTITUTE(A1,"/","")-1,9)+1

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[zebra7860]

There are some kind and brilliant people on this board. And I appreciate everybody's help