Skip to main content

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!

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. 👍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. 👍t3:


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


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. 👍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.


Reply