Как заставить Excel автоматически заполнять год в столбце вместо текущего года?
Контекст: Я ввожу данные предыдущего года в Excel.
Каждый раз, когда я набираю дату в столбце даты ( "9/16" на 16 сентября), Excel автоматически форматирует ее до "9/16/12", где 12 - это 2012 год, текущий год.
Я ввожу данные из прошлого года в текущем 2012 году. Я не хочу вводить "11" в 2011 году. Я хочу, чтобы Excel автоматически заполнял его, как это происходит с 2012 годом, и, как и в декабре 31-го.
Исправление простейшего заключается в том, чтобы установить часы в Windows в любое время в 2011 году, но это имеет тенденцию гадать с сетью, которая хочет вернуть меня и жалуется на то, что мой сетевой пароль вышел из дата и т.д.
Я предпочитаю, чтобы дата находилась в одном столбце, поэтому переход к альтернативным столбцам для дня/месяца/года для меня не является вариантом.
Можно было бы подумать, что это простое исправление, но через пару часов поиск и мой Google-fu терпят неудачу.
Ответы
Ответ 1
Быстрое исправление - просто ввести 9/16 и позволить Excel изменить его на 9/16/12. Затем, когда вы все закончите вводить свои даты, в новом столбце введите формулу =A1-365 и просто скопируйте формулу до конца, предполагая, что столбец A содержит введенные вами даты.
Одно наблюдение за этим: 2012 год високосный, поэтому для любых дат после 28 февраля, которые вы вводите, ваша формула должна быть =A1-365.25, иначе ваши даты будут отключены на один день. Это не должно быть 365,25, просто что-то больше 365 и меньше 366.
Это работает, потому что независимо от формата даты в ячейке Excel сохраняет фактическую дату как количество прошедших дней с 0 января 1900 года. (Да, январь 0 - это не настоящая дата, но Excel считает это есть.)
Ответ 2
То, что вы нашли, правильно, вы не можете изменить параметр Excel по умолчанию. Помимо изменения системной даты, вы можете использовать формулу рабочего листа:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
If IsDate(Target) Then
Application.EnableEvents = False
Target.Value = DateSerial(2011, Month(Target), Day(Target))
Application.EnableEvents = True
End If
End Sub
Ответ 3
- Выделите столбец дат в электронной таблице Excel, где вы хотите изменить год.
- На панели инструментов выберите "Формат", выберите "Ячейки" и в разделе "Категория" выберите "Дата". В столбце опций, который он предлагает для формата даты, выберите формат даты, который отображает месяц, день, запятую, пробел, 4-значный год (т.е.: 31 января 2011 г.). Убедитесь, что есть пробел перед 4-значным годом.
- Найдите команду "Заменить" в меню "Правка" (местоположение зависит от версии Mac или ПК и версии Excel).
- В окне "Заменить" в поле для данных, которые вы хотите заменить, введите этот 4-значный год, который вы хотите заменить. В другом поле для данных, которые вы хотите заменить старые данные, введите правильный год. Нажмите кнопку в этом окне для "Заменить все".
- Ваш выделенный столбец в электронной таблице теперь изменит все 4-значные годы на новый номер.
- Если вы не хотели менять все эти годы в этой колонке, я думаю, вы могли бы просто оставить некоторые из них не выделенными, и они были бы пропущены.
- После этого вы можете выделить столбец, вернуться в формат, ячейку, дату и reset формат даты обратно к тому, что вам нравится, например, 1/31/11.
Ответ 4
Спасибо за ответы - у меня была эта же проблема. Я хотел внести небольшую поправку в решение Stewbob. Как и OP, мне нужно было вводить данные за прошлый год без необходимости вводить "/11" каждый раз, но не все даты, которые я вводил, были в 2011 году (я также вводил даты в 2009 и 2010 годах, для которых я был бы чтобы вручную ввести год в любом случае, даже если я не откладывал в 2011 году!). Я бы не хотел, чтобы даты в '09 -10 были возвращены на год вместе с датами 2011 года.
Моим решением было создать второй соседний столбец со следующей формулой. Скажем, я набираю даты в колонку А и делаю столбец B моим "исправленным" столбцом; формула в столбце B:
= ЕСЛИ (А1 > 40908, ЕСЛИ (A1 > 40967, A1-366, A1-365), А1)
Таким образом, даты до 2011 года не будут исправлены, и мы также учитываем тот факт, что 2012 год является високосным, поэтому любые даты после 2/28 будут установлены на 366 дней вместо 365. Теперь я могу ввести любые даты как будто это все еще 2011 год. Это приятно, если вы не можете изменить дату/время в Windows, потому что ваше учреждение не дает вам административные привилегии на свой # #%%! рабочая станция.
Это может быть через несколько дней после первоначального сообщения, но я надеюсь, что это поможет.
Ответ 5
Я оставляю это здесь для всех, как я, которому помог JMax ответ. Версия Ths работает на всех листах книги (и поэтому должна быть помещена в рабочую книгу script, а не в листе), а также только изменения записей, которые содержат текущий год. Значения для текущего года и за последний год устанавливаются автоматически.
Dim This_Year As Integer
Dim Last_Year As Integer
Private Sub Workbook_Open()
This_Year = Year(Date)
Last_Year = This_Year - 1
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
If IsDate(Target) And Year(Target) = This_Year Then
Application.EnableEvents = False
Target.Value = DateSerial(Last_Year, Month(Target), Day(Target))
Application.EnableEvents = True
End If
End Sub
Ответ 6
Простое исправление. Вы просто вводите дату и месяц и даете системе ввести дату по умолчанию. После этого замените (Ctrl + H) 2012 на 2011 год.
Ответ 7
- Выберите столбец, содержащий даты
- Щелкните правой кнопкой мыши вверху и выберите "Формат ячеек"
- Выберите "Пользовательский"
- В окне "Тип" введите желаемый формат даты, но укажите год, в котором вы хотите появляться. В моем случае мне нужно было, чтобы даты были в 2018 году, но я ввожу данные в 2019 году. Поэтому я выполнил описанные выше шаги, чтобы установить собственный формат:
мм/дд/"2018"
Это работает как шарм без использования кода.
Ответ 8
Создайте новую ячейку с формулой ниже
=DATE(YEAR(B2)-1,MONTH(B2),DAY(B2))
и скопируйте только значение в исходную ячейку (B2)
