LinkBack URL
About LinkBacks
Hi,
Wanted to get assistance of adding the date Eg (today's date 26-09-2022 = 8+9+6 = 23 = 5
Regards
Raghavendra KR
Hi,
Wanted to get assistance of adding the date Eg (today's date 26-09-2022 = 8+9+6 = 23 = 5
Regards
Raghavendra KR
Just to be sure I've got this right, you want to total the numbers in the individual sections eg 26 = 2+6 = 8 and then total those sections and onwards until you end up with a single digit?
If someone has helped you then please add to their Reputation
Welcome to the forum.
=IF(DAY(A1)>9,LEFT(DAY(A1),1),0)+RIGHT(DAY(A1),1)+IF(MONTH(A1)>9,LEFT(MONTH(A1),1),0)+RIGHT(MONTH(A1),1)+LEFT(YEAR(A1),1)+MID(YEAR(A1),2,1)+MID(YEAR(A1),3,1)+RIGHT(YEAR(A1),1)
Set cell format to GENERAL.
Last edited by AliGW; 09-26-2022 at 06:49 AM. Reason: Typo corrected.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
@AliGW If I'm reading the original correctly then the result could then need adding again? ie that calc correctly gives 23 but the example seems to say we'd need to get 5 from that?
You can use Expression.Evaluate with Power Query but not with XL2007
Quick amendment to Ali's formula aboveOriginally Posted by AliGW
Another possible solutionFormula:
Please Login or Register to view this content.
I can't post the amended formula (forum glitch), but:@AliGW If I'm reading the original correctly then the result could then need adding again? ie that calc correctly gives 23 but the example seems to say we'd need to get 5 from that?
=LEFT(my_formula,1)+RIGHT(my_formula,1)
I like to think that I'm good with Excel and Maths in general but I would hugely appreciate it if you could explain that one to me? It works, perfectly but I have to admit to not being able to see howAnother possible solutionFormula:Please Login or Register to view this content.
its beautiful, I just dont understand how beautiful
It's based on the fact that the sum of the digits of any exact multiple of 9 will equal 9 (or a multiple thereof).
We need to subtract 1 from the start number & add it back on after otherwise is you start with 18 you end up with 0 instead of 9
Another way:
=MOD(TEXT(A1,"ddmmyyyy")-1,9)+1
HI experts,
Need a help on adding the dates to single number
Eg today's date 26-09-2022 = 8+9+6 = 23 = 5
Regards
Raghavendra KR
Easy enough with a custom function:
The in a cell:
Please Login or Register to view this content.
WBDPlease Login or Register to view this content.
Office 365 on Windows 11, looking for ✶ rep!
One way:Formula:Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Formula for cell B3Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Already asked & answered. https://www.excelforum.com/excel-for...le-number.html
Also reported as a duplicate.
With 365:Formula:Please Login or Register to view this content.
Administrative Note:
Welcome to the forum.
Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.
Please see Forum Rule #5 about thread duplication.
Because both threads have responses I am merging them. Please take the time to review our rules. There aren't many, and they are all important.
yes that correct what ever date is entered in the DD/MM/YYYY format must be added & then the final answer must be in Single digit
Have you tried any of the solutions already posted?
Thank you so much its working fine, will check with other combinations also.
Who are you talking to?
hope its not working for single digits eg., 1-1-2022 = 1+1+6 = 8
its working, but sometimes the double digit does not come down to single digit.
eg 29-12-2022 = 2 (final result)
Which solution are you using? When there are many posts offerings solutions you need to quote the one you are responding to.
There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks