год в excel

У меня два столбца года на листе excel в формате year.months (столбец сначала как 1.11 означает 1 год 11 месяцев), вторая колонка равна 0,7 означает 7 месяцев). Мне нужно разницу между двумя столбцами в формате year.months. т.е. 1.11-0.7 = 1.4 означает 1 год 4 месяца.

Всего 3 ответа


Этот подход будет выполнять математику, преобразовывая все в те же базовые единицы, что и месяц. Вы можете выполнить шаги над несколькими столбцами, что упростит чтение, обслуживание и устранение неполадок. Когда все работает, вы можете объединить его в единую формулу, которая будет чудовищем, которое трудно читать и потенциально поддерживать другими в будущем.

Ради этого решения предположим, что ваша дата 1.11 находится в A1, а дата 0.7 - в B1.

Шаг 1) Преобразуйте A1 и B1 в месяцы

В C1 введите следующую формулу и скопируйте ее в D1

=LEFT(A1,FIND(".",A1)-1)*12+RIGHT(A1,LEN(A1)-FIND(".",A1))

Шаг 2) Найдите разницу в месяцах

В E1 Поместите следующую формулу

=C1-D1

Шаг 3) Преобразование разницы в месяцах назад в формате year.month

В F1 найдем следующую формулу

=INT(E1/12)&"."&(E1-INT(E1/12)*12)

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

=INT(((LEFT(A1,FIND(".",A1)-1)*12+RIGHT(A1,LEN(A1)-FIND(".",A1)))-(LEFT(B1,FIND(".",B1)-1)*12+RIGHT(B1,LEN(B1)-FIND(".",B1))))/12)&"."&(((LEFT(A1,FIND(".",A1)-1)*12+RIGHT(A1,LEN(A1)-FIND(".",A1)))-(LEFT(B1,FIND(".",B1)-1)*12+RIGHT(B1,LEN(B1)-FIND(".",B1))))-INT(((LEFT(A1,FIND(".",A1)-1)*12+RIGHT(A1,LEN(A1)-FIND(".",A1)))-(LEFT(B1,FIND(".",B1)-1)*12+RIGHT(B1,LEN(B1)-FIND(".",B1))))/12)*12)

Я бы не хотел, чтобы кто-то новый приходил и смотрел на это и пытался понять, что его делает, не говоря уже о редактировании его через 3 месяца после изменения, когда требуется изменение.

POC

Несколько предостережений. Убедитесь, что дата в A больше, чем дата в B. Это дает плохие результаты для отрицательных значений. Также убедитесь, что всегда есть "." в день или ошибки. Если разница в 12 месяцев будет отображаться в 1.0 вместо разницы вместо 0.12. Если потребуется более поздняя версия, необходимо будет разработать специальный случай, когда есть различия, приводящие к кратности 12 различий.


Работа с DatedIF

Предположим, что дата начала находится в B1, а дата окончания - в A1.

Шаг 1) Преобразование даты

Преобразуйте строку в фактическую дату исполнения excel

В C1 и используйте следующую формулу и скопируйте в D1

=DATE(LEFT(A1,FIND(".",A1)-1),RIGHT(A1,LEN(A1)-FIND(".",A1)),1)

Преобразование предполагает первый месяц

Шаг 2) Определите разницу в месяцах

Поместите следующее в E1

=DATEDIF(D1,C1,"M") 

Обратите внимание, что дата начала должна быть продолжена во времени, чем дата окончания, или вы получите #NUM-ошибку.

Шаг 3) Преобразуйте обратно в формат строки для ym

Поместите следующее в F1

=INT(E1/12)&"."&(E1-INT(E1/12)*12)

и объединены в уродство формулы одной клетки:

=INT((DATEDIF(DATE(LEFT(B1,FIND(".",B1)-1),RIGHT(B1,LEN(B1)-FIND(".",B1)),1),DATE(LEFT(A1,FIND(".",A1)-1),RIGHT(A1,LEN(A1)-FIND(".",A1)),1),"M"))/12)&"."&((DATEDIF(DATE(LEFT(B1,FIND(".",B1)-1),RIGHT(B1,LEN(B1)-FIND(".",B1)),1),DATE(LEFT(A1,FIND(".",A1)-1),RIGHT(A1,LEN(A1)-FIND(".",A1)),1),"M"))-INT((DATEDIF(DATE(LEFT(B1,FIND(".",B1)-1),RIGHT(B1,LEN(B1)-FIND(".",B1)),1),DATE(LEFT(A1,FIND(".",A1)-1),RIGHT(A1,LEN(A1)-FIND(".",A1)),1),"M"))/12)*12)

Скажем, A1=1.11 и B1=0.7 тогда формула будет:

=LEFT(A1;FIND(".";A1;1)-1)-LEFT(B1;FIND(".";B1;1)-1)&"."&(MID(A1;FIND(".";A1;1)+1;2)-MID(B1;FIND(".";B1;1)+1;2))

введите описание изображения здесь


Есть идеи?

10000