problem with double digit numbers (10+) registering as single digit numbers in argument

[hholt007]

Hello, I am trying to categorize specific elapsed times into general time frames via the following argument (in German): =WENN(S2>="6 Months";"6+ Months";WENN(S2>="3 Months";"3 to < 6 Months";WENN(S2>="2 Months";"2 to < 3 Months";"1 to <2 Month"))).  The argument works as desired until an the elapsed time exceeds 10 Months.  It then sorts based on the first digit only, as you can see.  I have tried changing the format of the cells, which did not work.  Would be grateful for advice on how to fix this.  Thank you in advance!

[#2]

@hholt007 As you noticed, you are dealing with texts, thus "10" gets sorted before "3". The easiest way out would be to change the single digit months into "01 Months", "02 Months" up to "09 Months". Then your formula should work. Alternatively, split the column and work with real numbers.

[#3]

@Riny_van_Eekelen Thank you for your reply. The specific times elapsed are also based off of a formula: =DATEDIF(C2;A2;"M") & " Months, " & DATEDIF(C2;A2;"MD") & " Days". Therefore, I am not really sure how to add a leading 0 to the single digit months. Is there an easy way to format them as you have suggested? Using 00 under the "user defined section" of cell formatting does not work in this case. Thank you.

[#4 · Accepted solution]

@hholt007 I believe this will work: for you:

=TEXT(DATEDIF(C2;A2;"M");"00") & " Months, " & DATEDIF(C2;A2;"MD") & " Days"

[#5]

@Riny_van_Eekelen Yes, this works. Thank you for your quick responses and helping to resolve this!

[#6]

@hholt007 Glad I could help!