Need to be able to add and subtract months and days formatted as MM-DD text in excel

Hi, welcome to the forum

So, this is not really days and months, it is just 2 sets of values, that have certain restrictions placed on them?

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

This formula would be one way for the 34-32 type numbers.

Formula:

Please Login or Register  to view this content.

Unfortunately I cannot follow the logic of the rest of the sheet.

Originally Posted by Lynx_TWO

That gets me closer, but it's still too easy to break. What if I simply create a lookup table on another sheet, like this: Attachment 403577

How would the calculations work off that sheet? The excel spreadsheet would be larger but the formula should be much easier...

Thanks for all your help so far!

Too easy to break. Have you tried to break it? Can you give an example of a "##-##" where it does break? I can see where a three digit leading number wouldn't work with the above formula (examples in the new lookup table)....but that can be modified.

Formula:

Please Login or Register  to view this content.

That works with 133-67 as well as 33-67. In cases where leading numbers are 1 to 12 the data itself will have to be entered as text (a leading ' should do it) or Excel would read your data as dates. Without that in those cases this formula won't work.

How would the calculations work off that sheet?

I don't see where the lookup table will be easier. For example there are no provisions for cases where the right numbers are larger than 29. Again even this recent formula can be modified to accommodate those right numbers that are larger than even 99. I.e. the lookup table would have to be exhaustive of all possibilities. I don't see "easier" with a lookup table. The math conversions still have to be made.

BTW: Originally the data provided included only two digits on both sides of the "-". The above now takes care of 1, 2, 3, 4, 5 .... or more digits left of "-". How many digits do you anticipate right of "-"?

A more thorough range of examples of the "-" combinations you anticipate would be helpful. Then we can see where the formula breaks.

If you weren’t aware of it formatting is cosmetic. It only changes the presentation of numbers into forms that we can recognize as readable. They mean nothing to Excel. Excel sees only numbers.

If there is a way to invent a custom format built upon a custom numbering system and retain the real values I am unaware of it. You have a custom numbering system that is a mix of base 10 and base 30. As far as I can see your “formatting” will need to be coerced in the last step and not through Format Manager. The downside is that the resulting numeric values will not be there. You will need to resort to string manipulations to order to extract usable numbers at that point.

In the attached I reset the workbook formatting to General. Setting it to Text introduces complications that are a real pain …… especially if you are going to mix the data as text and numbers as in your last upload.

I then re-entered the data as 3 numbers followed by “-“ followed and then followed by 2 numbers …. leading zeroes included. Excel will read these as text by default without need of invoking Format Manager.
The below shows what I mean.

Row\Col	E	F	G	H
1	004-11	004-11	004-11
2	128-11	128-22	000-23
3	132-22	132-33	004-34	In E3:G3 =TEXT(SUM(LEFT(E1,3),LEFT(E2,3))&SUM(RIGHT(E1,2),RIGHT(E2,2)),"000-00")
4
5	132-22	133-03	005-04	In E3:G3 =TEXT(LEFT(E3,3)+INT(RIGHT(E3,2)/30),"000")&TEXT(MOD(RIGHT(E3,2),30),"-00")

Does this look like what you want?

I've been working on another approach that uses helper columns to isolate the math steps from the "formatting" that I mentioned in my last post.

Row\Col	A	B	C	D	E	F	G
1	Months-Days	Days only	Months-Days	Days only	Months-Days	Days only
2	004-11	131	044-13	1333	002-48	108	In B2:B3, D2:D3 & F2:F3 =SUMPRODUCT(LEFT(A2,3)*30)+SUMPRODUCT(--RIGHT(A2,2))
3	132-67	4027	000-02	2	045-01	1351
4
5	Total Days	4158		1335		1459	In B5, D5 & F5 =SUM(B2:B3)
6	Converted	138-18		044-15		048-19	In B6, D6 & F6 =TEXT(INT(B5/30)&MOD(B5,30),"000-00")