Multiple Records, Sum by Individual

[Cathy_Crosman]

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!

[M_Godfrey · Accepted solution]

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:

[Cathy_Crosman]

That is phenomenal! I’ll have to dissect that to see how it works, but it works beautifully! Thank you so much.

[kuovonne]

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.