Hi - I would like to be able to sum the individual digits in a date - e.g, a given date = the sum of its individual digits. Some examples:
Date: 7/13/1969 = 7 + 1 + 3 + 1 + 9 + 6 + 9 = 36
Date: 12/20/2019 = 1 + 2 + 2 + 0 + 2 + 0 + 1 + 9 = 17
I have some idea that I could convert the dates to text, then do a “value” of each digit, but nothing I’ve tried has worked. I’m grateful for any ideas!
Best answer by M_Godfrey
Hi @Cathy_Crosman! Welcome to the community! :partying_face: :partying_face: :partying_face:
This formula does what you are looking for:
IF(Date, RIGHT(DATETIME_FORMAT(Date, 'MM', 1)) + LEFT(DATETIME_FORMAT(Date, 'MM', 1)) + RIGHT(DATETIME_FORMAT(Date, 'DD', 1)) + LEFT(DATETIME_FORMAT(Date, 'DD', 1)) + RIGHT(DATETIME_FORMAT(Date, 'YYYY', 1)) + LEFT(DATETIME_FORMAT(Date, 'YYYY', 1)) + VALUE(MID(DATETIME_FORMAT(Date, 'YYYY'), 2, 1)) + VALUE(MID(DATETIME_FORMAT(Date, 'YYYY'), 3, 1)))
Replace ‘Date’ in the formula with your date field.
All the best with it. :+1:t3:
Hi @Cathy_Crosman! Welcome to the community! :partying_face: :partying_face: :partying_face:
This formula does what you are looking for:
IF(Date, RIGHT(DATETIME_FORMAT(Date, 'MM', 1)) + LEFT(DATETIME_FORMAT(Date, 'MM', 1)) + RIGHT(DATETIME_FORMAT(Date, 'DD', 1)) + LEFT(DATETIME_FORMAT(Date, 'DD', 1)) + RIGHT(DATETIME_FORMAT(Date, 'YYYY', 1)) + LEFT(DATETIME_FORMAT(Date, 'YYYY', 1)) + VALUE(MID(DATETIME_FORMAT(Date, 'YYYY'), 2, 1)) + VALUE(MID(DATETIME_FORMAT(Date, 'YYYY'), 3, 1)))
Replace ‘Date’ in the formula with your date field.
All the best with it. :+1:t3:
Hi @Cathy_Crosman! Welcome to the community! :partying_face: :partying_face: :partying_face:
This formula does what you are looking for:
IF(Date, RIGHT(DATETIME_FORMAT(Date, 'MM', 1)) + LEFT(DATETIME_FORMAT(Date, 'MM', 1)) + RIGHT(DATETIME_FORMAT(Date, 'DD', 1)) + LEFT(DATETIME_FORMAT(Date, 'DD', 1)) + RIGHT(DATETIME_FORMAT(Date, 'YYYY', 1)) + LEFT(DATETIME_FORMAT(Date, 'YYYY', 1)) + VALUE(MID(DATETIME_FORMAT(Date, 'YYYY'), 2, 1)) + VALUE(MID(DATETIME_FORMAT(Date, 'YYYY'), 3, 1)))
Replace ‘Date’ in the formula with your date field.
All the best with it. :+1:t3:
That is phenomenal! I’ll have to dissect that to see how it works, but it works beautifully! Thank you so much.
+27Hi @Cathy_Crosman! Welcome to the community! :partying_face: :partying_face: :partying_face:
This formula does what you are looking for:
IF(Date, RIGHT(DATETIME_FORMAT(Date, 'MM', 1)) + LEFT(DATETIME_FORMAT(Date, 'MM', 1)) + RIGHT(DATETIME_FORMAT(Date, 'DD', 1)) + LEFT(DATETIME_FORMAT(Date, 'DD', 1)) + RIGHT(DATETIME_FORMAT(Date, 'YYYY', 1)) + LEFT(DATETIME_FORMAT(Date, 'YYYY', 1)) + VALUE(MID(DATETIME_FORMAT(Date, 'YYYY'), 2, 1)) + VALUE(MID(DATETIME_FORMAT(Date, 'YYYY'), 3, 1)))
Replace ‘Date’ in the formula with your date field.
All the best with it. :+1:t3:
Great formula, and the same approach I was thinking of.
I noticed that you use VALUE with MID but not with RIGHT or LEFT. Is there a reason for this?
If the user sometimes gets unexpected results, the formula might want to include setting the timezone in DATETIME_FORMAT.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
