DATEDIF() Не работает со второй строкой дат TEXTSPLIT

У меня есть ряд разных диапазонов дат, разделенных подчеркиванием. Эти даты заключены в одну ячейку Excel, поэтому они отображаются в разных строках. Пожалуйста, ознакомьтесь с образцами деталей ниже:

CELL B2
----------------------------
| 06/13/2019 - 09/01/2019_ |
| 06/21/2021 - 08/29/2021_ |
| 08/22/2022 - 11/30/2022_ |
| 06/30/2023 - 07/28/2023_ |
| 10/03/2023 - 11/30/2023  |
----------------------------

Это Excel TEXTSPLIT, который я использую =IFERROR(TEXTSPLIT(C2,,"_"),""), и результат такой:

CELL C
 ----------------------------
2| 06/13/2019 - 09/01/2019_ |
 ----------------------------
3| 06/21/2021 - 08/29/2021_ |
 ----------------------------
4| 08/22/2022 - 11/30/2022_ |
 ----------------------------
5| 06/30/2023 - 07/28/2023_ |
 ----------------------------
6| 10/03/2023 - 11/30/2023  |
 ----------------------------

Мне нужно вычислить каждый DATEDIF, но только CELL C2 дал результат. Допустим, 2 месяца. Вот формула, которую я использую:

CELL D2

=IFERROR(DATEDIF(IFERROR(LEFT(C2,SEARCH(" - ",C2)-1),""),IFERROR(RIGHT(C2,SEARCH(" - ",C2)-1),""),"YM"),"")&" Months"

Остальные ничего не возвращают, поскольку значение является ошибкой.

Я пытался искать в Интернете, но решения не было найдено.

Спасибо вам за помощь.

Вам нужно избавиться от возврата каретки после _, что и вызывает проблему. =SUBSTITUTE(IFERROR(TEXTSPLIT(C2,,"_"),""),CHAR(10),"")

— 09.05.2024 19:14

Вы могли бы использовать эту формулу: =BYROW(--TEXTSPLIT(TEXTAFTER(" - "&TEXTSPLIT(C2,"_",CHAR(10),1)," - ",{1,2})," - "),LAMBDA(x, DATEDIF(TAKE(x,,1),TAKE(x,,-1),"YM"))) но я не понимаю ваш ожидаемый результат, почему вы используете DATEDIF() функцию, согласно MSFT эта функция не должна использоваться

— 09.05.2024 19:17

Альтернативный метод: =MAP(TEXTSPLIT(C2,"_",CHAR(10),1),LAMBDA(α, LET(δ, --TEXTSPLIT(α," - "), INT((YEARFRAC(TAKE(δ,,1),TAKE(δ,,-1),1)*12))))) этот 120 байт, пока последний был 132

— 09.05.2024 19:49

Нет необходимости в карте при использовании YEARFRAC: =LET(t,TEXTSPLIT(C2," - ",CHAR({95;10}),1),YEARFRAC(TAKE(t,,1),DROP(t,,1),1)*12)

— 09.05.2024 23:35

Вы можете поместить подчеркивание и CHAR(10) в массив, не используя CHAR, но не используя мобильную версию Excel. {"_","&"} где следует заменить амперсанд на Ctrl+enter. Вы можете обновить это в ответе.

— 09.05.2024 23:42

excel excel-formula

09.05.2024 18:57

Преобразование HTML-таблицы в профессиональный документ Excel

Это самый простой способ создания Excel из HTML-таблицы.

Импорт excel в laravel в базу данных

Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете попробовать одно из следующих действий: проблема, с которой вы столкнулись, связана с возвратом каретки, как уже было сказано Скоттом Крейнером Сэр, поэтому вам также необходимо удалить эту часть, в которой говорится, попробуйте одно из следующих действий:

• Формула, используемая в ячейке B2

=BYROW(--TEXTSPLIT(TEXTAFTER(" - "&TEXTSPLIT(A1,"_",CHAR(10),1)," - ",{1,2})," - "),LAMBDA(x, 
   TEXT(DATEDIF(TAKE(x,,1),TAKE(x,,-1),"YM"),"0 \M\o\n\t\h\s;;")))

Или использование MAP() с функцией YEARFRAC()

=MAP(TEXTSPLIT(A1,"_",CHAR(10),1),LAMBDA(α,
  LET(δ,--TEXTSPLIT(α," - "),TEXT(INT((YEARFRAC(TAKE(δ,,1),
 TAKE(δ,,-1),1)*12)),"0 \M\o\n\t\h\s;;"))))

_{NOTE: The use of TEXT() function which takes care of the singular and plural for those where it returns 0.}

Забудьте о том, что я написал выше, просто используйте следующее, как прокомментировал и спросил P.b Сэр, чтобы опубликовать:

=LET(t,TEXTSPLIT(A1," - ",{"_";"
"},1),TEXT(INT(YEARFRAC(TAKE(t,,1),DROP(t,,1),1)*12),"0 \M\o\n\t\h\s;;"))

_{NOTE: That CHAR({95;10}) refers to this {"_";" "} (other than the underscore may reflect as space, but one can see by clicking on edit of my answer as well), how arrived it by selecting the CHAR() and hit F9 (Function Key) press ok. For more information please refer the commented above by @P.b Sir .}

09.05.2024 20:32

Просто ради интереса (используя YEARFRAC в формате г/м/д)

Определите dif_from_dates_string для использования в нескольких местах или используйте функцию напрямую;

=LAMBDA(dates_from_string,
  BYROW(TEXTSPLIT(dates_from_string," - ","_" & CHAR(10)),
    LAMBDA(dates,
      LET(
        fr,YEARFRAC(INDEX(dates,,1),INDEX(dates,,2)),
        y,TRUNC(fr),m,TRUNC((fr-y)*12),d,TRUNC((fr-y-IF(m,m/12))*360),
        TEXTJOIN(" ",,IF(y,y&"y",""),IF(m,m&"m",""),IF(d,d&"d",""))
      )
)))

Затем введите f.ex. в ячейке C2 (должно быть как минимум столько же строк в диапазоне) =dif_from_dates_string(B2)

результат