На жаль, стандартні функції Excel не дозволяють конвертувати будь-яке шестизначне число в дату з такими ж значеннями дня, місяця і року. Але існує спосіб зробити це самостійно з використанням текстових формул.
Припустимо, у нас є таблиця, яку експортували з сторонньої бази даних. Дати в цій таблиці наведені так само, як зазначено в питанні.
Крім того, ми використовуємо функції ЛЕВСИМВ, ПРАВСИМВ і ПСТР. Всі ці функції беруть із осередку з текстом вказане в аргументі кількість символів, починаючи з заданої в аргументі позиції. наприклад,
Витягує з осередку з значенням 221103 два символу зліва (тобто 22). Функція ПРАВСИМВ робить те ж, але відраховує справа, а функція ПСТР - зліва, але витягує потрібну кількість символів, попередньо відрахувавши зазначену їй позицію. Тому за допомогою ПСТР можна витягати символи з середини тексту.
За допомогою, цих трьох функцій ми ізвлечём окремі частини дати (22, 11, 03), за допомогою операнда і знака / склеим їх разом, а за допомогою функції значить перетворимо вийшло вираженні в число:
Ми спеціально використовували функції ЛЕВСИМВ, ПРАВСИМВ і ПСТР одночасно, щоб продемонструвати їх роботу. Але можливо використовувати тільки функцію ПСТР, правильно поставивши позиції символів:
Цей метод також чудово підходить для ситуації, коли вивантажені дати знаходяться в форматі MM / ДД / РР або ГГ / ММ / ДД - досить правильно вказати в нашій формулі позиції видобутих сегментів тексту або розташувати функції ЛЕВСИМВ, ПРАВСИМВ і ПСТРв потрібному нам порядку.
Я роблю розрахунки для диплома, і мені потрібно вивести негативні значення дат і часу. Як це зробити?
Excel працює тільки з позитивними значеннями дат і часу, інакше користувач отримує помилку ##### (див. Помилки Excel). Але оскільки обов'язково знайдуться бажаючі подолати обмеження, ми можемо порадити наступне способи.
Спосіб 1: Вивести результат обчислень як текст, використовуючи функцію ТЕКСТ.
Наприклад, ми обчислюємо різницю між двома датами в осередках А1 і А2
Якщо просто забрати від меншої дати більшу, ми отримаємо число -29848. За допомогою функції ТЕКСТ, ми можемо перетворити число 29848 в дату зі знаком мінус:
Тобто ми спочатку знайшли позитивну різницю між датами (29848), а потім перетворили її в текст з форматом дати і знаком мінус.
Якщо потрібно вивести від'ємне значення часу, необхідно використовувати формат часу:
Повний список форматів можна знайти у вбудованій допомоги по функції ТЕКСТ.
Зверніть увагу, якщо різниця двох чисел буде спочатку негативною, функція ТЕКСТ поверне помилку. Для вирішення цієї проблеми можна використовувати конструкцію:
Тобто ми завжди спочатку обчислюємо позитивну різницю, а потім форматіруем негативну дату / час.
Не забувайте, що отриманий результат є текстом, тобто його неможливо використовувати в подальших обчисленнях!
В цьому випадку ми створюємо для користувача формат дати / часу зі знаком мінус. Дл створення формату потрібно викликати вікно Формат ячеек будь-яким зручним для Вас способом. У списку Числові формати необхідно вибрати пункт (всі формати). Після цього в списку Тип ми вибираємо числовий формат ДД.ММ.РРРР ч: мм, який ми будемо налаштовувати.
Оскільки цей формат є вбудованим, після настройки Excel створить копію цього числового формату. Вихідний формат в списку Тип неможливо змінити або видалити.
Після вибору зі списку потрібного формату, він з'явиться в полі Тип. Виправляємо цей формат на наступний: -ДД.ММ.ГГГГ ч: мм. і натискаємо Ок.
Залишилося тільки відформатувати осередки з потрібними датами / часом із застосуванням нового формату.
Пам'ятайте, що вихідне число має бути позитивним, інакше Excel поверне помилку!
Я готую зарплатні відомості і мені потрібно постійно вводити в таблицю останнє число місяця і розраховувати, який це буде день тижня. Це можна якось автоматизувати?
Для такого завдання використовується функція КОНМЕСЯЦА:
- нач_дата - це дата, від якої буде відраховуватися кінець місяця,
- чісло_месяцев - порядковий номер місяця, в якому буде розрахована остання дата, відрахований від початкової дати.
Щоб дізнатися, який це буде день тижня, потрібно використовувати функцію ДЕНЬ:
А як визначити, чи є дата вихідним днем?
Для цього можна використовувати функцію ДЕНЬНЕД:
Тобто ми розраховуємо порядковий номер дня тижня, який відповідає заданій датою, і якщо цей день має номер 6 або 7 (субота або неділя), то наша формула поверне нам значення ІСТИНА, що означає, що наша дата - вихідний.