Як розрахувати останній (або перший) день місяця за формулою ExcelNova
Будь-хто, хто створює звіти, що самостійно оновлюються, зазвичай стикається з тим, як автоматично створювати дати, наприклад кінець місяця або перше місяця.
Excel пропонує нам два варіанти розрахунку в кінці місяця:
- За формулою КІНЦЯ МІСЯЦЯ ()
- З формулою DATE () та невеликою хитрістю
З другим варіантом ви також можете знайти перше числа місяця!
У цьому дописі я поясню ці дві функції.
Для обчислення останнього дня місяця Excel спеціально має формулу МІСЯЦЬ КІНЦЯ (). Це вже було доступно в надбудові функцій аналізу, саме тому надбудову спочатку потрібно було активувати, щоб використовувати її. Однак, починаючи з Excel 2013, це вже з’явилося у стандартному діапазоні формул.
Пояснено синтаксис MONTH END ():
Синтаксис формули такий:
= Кінець місяця (дата початку; місяці)
Початкова дата Аргумент - це як випливає з назви, дата початку, на основі якої обчислюється останній день місяця. На практиці це, як правило, поточна дата або бажана дата початку проекту, для якого слід розрахувати останній день майбутнього місяця.
Місяці Аргументом є кількість місяців до або після дати початку. Місяць, у якому лежить дата початку, завжди тут 0! Можуть використовуватися як позитивні, так і негативні значення.
Поточний приклад = КІНЕЦЬ МІСЯЦЯ (“29.01.2015”; 0) повертає останній день січня 2015 року: 31.01.2015
Обчисліть кінець місяця в майбутньому
Наприклад, встановивши місяці на 3, ми повідомляємо формулу, що нам потрібен останній день місяця, тобто 3 місяці в майбутньому.
Знову ж: січень = 0, лютий = 1, березень = 2, квітень = 3
Формула = КІНЕЦЬ МІСЯЦЯ (“29.01.2015”; 3) повертає 30.04.2015.
Обчисліть кінець місяця в минулому
Те саме стосується останніх кількох місяців. Тепер ми просто рахуємо назад.
За допомогою чого ми отримуємо 30.11.2014 повернуте з Excel.
За допомогою DATE ми можемо досягти того самого, що КІНЦЯ МІСЯЦЯ, і навіть з меншими зусиллями, особливо коли ми створюємо звіт, в якому ми повинні зобразити кілька закінчень місяців.
MONTH () - це стандартна функція дати та часу, і використовується для створення дати за допомогою формули або вручну.
Пояснено синтаксис MONTH ():
Синтаксис формули DATE виглядає так:
= ДАТА (рік; місяць; день)
Аргументи у цій формулі самі собою пояснюються: Аргумент рік використовується для встановлення року в даті, яку потрібно створити. місяць використовується близько місяця, і День встановити день.
Приклад формули з DATE ()
У цьому прикладі 2015 рік встановлений як константа. Щоб нам не потрібно було вводити число кожного місяця вручну, коли ми копіюємо формули, я використав формулу ROW () і прив’язав її до комірки A1.
Обчисліть перший день місяця
Якби ми ввели = DATE (2015; рядок (A1); 1), Excel оцінив би це як 01.01.2015.
Таким чином, створення першого числа місяця за допомогою формули є відносно простим.
Обчисліть останній день місяця
Розрахувати останній день місяця так само просто. Все, що вам потрібно зробити, це зрозуміти, як Excel обчислює дати.
Якщо ви хочете обчислити перший день місяця з DATE, введіть 1 для аргументу day. Якщо ви хочете 2-го, це 2-го і т.д.
Excel розуміє дні як числовий рядок. Цифрова лінія може бути не лише зростаючою, але й спадною. Тому ми також можемо ввести 0 або -1 тощо.
Тоді в Excel 0-й день місяця розуміється як день, що настає до 1-го числа місяця. Іншими словами, останній день попереднього місяця! Ми можемо цим скористатися.
Якщо ми хочемо розрахувати останній день січня, формула буде виглядати так:
Результат - 31 січня 2015 року.
Якщо ми Формула все ще динамічна хочу створити це виглядає так:
Якщо скопіювати цю формулу в будь-яку комірку нового робочого аркуша та скопіювати її вниз, вона обчислить кінець кожного місяця за 2015 рік!
У Китаї Комуністична партія забороняє згадувати в Інтернеті річницю різанини Тяньаньмень (4 червня 1989 р.). Тому винахідливий китайський деякий час використовував код, коли писав про це у своїх блогах: 35 червня.
Звичайно, Excel правильно оцінює цю дату:-)