# Sum individual digits of dates

Topic Labels: Formulas
Solved
368 3
cancel
Showing results for
Did you mean:  4 - Data Explorer

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!

1 Solution

Accepted Solutions  5 - Automation Enthusiast

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:

3 Replies 3  5 - Automation Enthusiast

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:  4 - Data Explorer

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

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