Mar 29 2022 04:33 AM
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!
Mar 29 2022 04:45 AM - edited Mar 29 2022 04:46 AM
@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.
Mar 29 2022 05:11 AM
Mar 29 2022 05:14 AM
Solution@hholt007 I believe this will work: for you:
=TEXT(DATEDIF(C2;A2;"M");"00") & " Months, " & DATEDIF(C2;A2;"MD") & " Days"
Mar 29 2022 05:28 AM
Mar 29 2022 05:31 AM
@hholt007 Glad I could help!
Mar 29 2022 05:14 AM
Solution@hholt007 I believe this will work: for you:
=TEXT(DATEDIF(C2;A2;"M");"00") & " Months, " & DATEDIF(C2;A2;"MD") & " Days"