Vba, чтобы создать день наступления (а не День недели), т. Е. День 1, День 2 и т. Д. По дате

Макрос vbaexcel для изменения моего отчета от текущего до требуемого формата в прилагаемых листах.

Резюме

Я хотел бы добавить столбец как «День появления» — то есть: День 1 … День Z на основе столбца даты / времени, чтобы иметь возможность использовать сводную таблицу для идентификации всех кодов, которые использовались в каждый день услуги для всего пребывания за каждый номер счета.

Есть 40K строк номера счета с различными днями услуг. Количество столбцов должно оставаться таким же, как в примере.

Текущий рабочий лист

Текущий рабочий лист

Желаемый рабочий лист

Желаемый рабочий лист

Исходя из того, что 1-й день является самым ранним днем ​​в вашей колонке «Дата и время», и этот столбец находится в столбце «Е», эта формула будет делать это ="Day " & E2-MIN(E:E)+1 . Очевидно, что это вводится в любой столбец, который вы хотите, в строке 2, а затем заполняется.

Предполагая, что:

  • Номер счета в столбце A

  • день возникновения в колонке C

  • дата и время в столбце E

А также предполагая, что день наступления не является «днем месяца», а просто последовательностью разных дней (если это не так, просто используйте комментарий Скотта).

Вы можете использовать эту формулу, чтобы получить желаемый номер дня, но без префикса «День».

Введите эту формулу в ячейку C2, затем скопируйте и вставьте ее во все ячейки столбца C:

 = IF(A2<>A1, 1, IF(E2=E1, C1, C1+1)) 

Если вы настаиваете на том, чтобы отображать его как "Day x" , формула немного сложнее, но она работает (снова введите эту формулу в ячейку C2, затем скопируйте ее / вставьте в другие ячейки столбца C):

 =IF(A2<>A1,"Day 1",IF(E2=E1,C1,"Day "& (1+RIGHT(C1,LEN(C1)-4)))) 

ps: Я бы не рекомендовал его префикс, так как это числовые данные, поэтому пусть он останется числовым …

Это может быть излишним …. но это то, что я придумал ….

 Function DayOfOccurrence(InputDate As Date) As String YearEntry = Year(InputDate) MonthEntry = Month(InputDate) IsLeapYear = -1 On Error Resume Next IsLeapYear = CDate("2 / 29 /" & YearEntry) 'Hack to see if it's a leap year. Variable IsLeapYear will remain -1 if NOT a leap year. On Error GoTo 0 If IsLeapYear = -1 Then 'Is Not a Leap Year If MonthEntry = 1 Then Base = 0 ElseIf MonthEntry = 2 Then Base = 31 ElseIf MonthEntry = 3 Then Base = 59 ElseIf MonthEntry = 4 Then Base = 90 ElseIf MonthEntry = 5 Then Base = 120 ElseIf MonthEntry = 6 Then Base = 151 ElseIf MonthEntry = 7 Then Base = 181 ElseIf MonthEntry = 8 Then Base = 212 ElseIf MonthEntry = 9 Then Base = 243 ElseIf MonthEntry = 10 Then Base = 273 ElseIf MonthEntry = 11 Then Base = 304 ElseIf MonthEntry = 12 Then Base = 334 End If Else 'Is a Leap Year If MonthEntry = 1 Then Base = 0 ElseIf MonthEntry = 2 Then Base = 31 ElseIf MonthEntry = 3 Then Base = 60 ElseIf MonthEntry = 4 Then Base = 91 ElseIf MonthEntry = 5 Then Base = 121 ElseIf MonthEntry = 6 Then Base = 152 ElseIf MonthEntry = 7 Then Base = 182 ElseIf MonthEntry = 8 Then Base = 213 ElseIf MonthEntry = 9 Then Base = 244 ElseIf MonthEntry = 10 Then Base = 274 ElseIf MonthEntry = 11 Then Base = 305 ElseIf MonthEntry = 12 Then Base = 335 End If End If DayOfOccurrence = "Day " & Base + Day(InputDate) End Function 

Я добавлю следующее:

Используйте следующую формулу в C:

 ="DAY " & DAY(E2 - MIN(IF(A:A = A2,E:E)))+1 

Тогда не имеет значения, запущен ли заказ.

Чтобы построить комментарий @pnuts, если вы хотите сохранить базовый номер в виде числа, используйте эту формулу:

 =DAY(E2 - MIN(IF(A:A = A2,E:E)))+1 

И используйте следующий настраиваемый формат в диапазоне:

 "Days " 0 

Он поместит слово перед номером, но сохранит номер в качестве записи, чтобы можно было выполнить любую математику без удаления слов.

Вы можете использовать эту настраиваемую функцию в VBA. Это даст вам следующий день в году.

 Function d_day_consequent(my_date As Date) d_day_consequent = my_date - DateSerial(Year(my_date), 1, 1) End Function 

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

Давайте будем гением компьютера.