Формула для преобразования числа в дату - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Формула для преобразования числа в дату - Мир MS Excel

Старая форма входа

Страница 1 из 2 1 2 »
Модератор форума: китин, _Boroda_

Формула для преобразования числа в дату

Формула для преобразования числа в дату

Дата: Пятница, 16.11.2012, 11:58 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

Поле(поля) содержит ЧИСЛО(А) необходимо в новой колонке получить в стандартном виде ДАТУ.
При том, что ЧИСЛО - это КОЛИЧЕСВТО по 100 наносекунд прошедших с 1.1.1601

(немного попереводя единицы:
1 минута 600000000 РАЗ по 100 наносекунд
1 час 36000000000 РАЗ по 100 наносекунд
1 сутки 864000000000 РАЗ по 100 наносекунд)

Ответить

Дата: Пятница, 16.11.2012, 12:12 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Quote (ALARMus)

прошедших с 1.1.1601

Excel, в отличии от VBA, не работает с датами ранее 01.01.1900

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Пятница, 16.11.2012, 12:17 | Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

Ну это не суть пусть 1900.
В xls я схему отработаю, а код (точнее его логика) будет применена в другом ПО. Важно само преобразование.

Ответить

Дата: Пятница, 16.11.2012, 12:18 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Quote (ALARMus)

Важно само преобразование

Code

=ДАТА(1900;1;1+(A1/864000000000))

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Пятница, 16.11.2012, 12:24 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 181

Репутация: 0 ±

Замечаний: 20% ±

Quote (Serge_007)

=ДАТА(1900;1;1+(A1/864000000000))

Немного не то получилось. У меня есть проверочные данные
130118256000000000 это 1.5.2013
130171824000000000 это 2.7.2013
Но от 1.1.1601 - ну это не суть должно просто отличаться на 299 лет.

но при этом использовал Вашу формулу и получил для обеих строк 16.09.1989 (хотя вроде даты разные должны быть)

Ответить

Дата: Пятница, 16.11.2012, 12:41 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3691

Репутация: 790 ±

Замечаний: 0% ±

365

Я думаю превышены возможности Экселя.
Если нолик удалить - уже получаются нормальные даты
25.03.1941
01.04.1941
имхо нужно входящие данные сперва (вне Экселя) разделить на 1000, затем из полученного уже делать даты.

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Пятница, 16.11.2012, 12:51 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Quote (Pelena)

и перевести в дни, то получится меньше суток

Получится 62 дня

Quote (Hugo)

думаю превышены возможности Экселя

Да, Excel работает только до 15 разрядов значимых чисел

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Пятница, 16.11.2012, 13:06 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 19405

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Quote (Serge_007)

Получится 62 дня

Не учла "по 100 наносекунд"

А если просто разделить исходное число на 864000000000 и перевести в дату

Code

=A1/864000000000

Получим 28.04.2312

Quote (ALARMus)

должно просто отличаться на 299 лет

Остаётся разница с тестом в три дня

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Пятница, 16.11.2012, 13:08 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3691

Репутация: 790 ±

Замечаний: 0% ±

365

Я не математик, поэтому как высчитать в Экселе - голову ломать не стал.
Но UDF считает вроде верно - хотя Ваши даты не получаются.

[vba]

Code

Function mydata(r As Range)
     Dim x As Double
     x = r.Value    ' * 1000
     mydata = CDate(1 + (x / 864000000000#))
End Function

[/vba]
Можно входящее делить на 1000, или не делить (изменив код) - результат одинаков:
29.04.2312
30.06.2312

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Пятница, 16.11.2012, 13:12 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 19405

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Почему +1?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Пятница, 16.11.2012, 13:14 | Сообщение № 11

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3691

Репутация: 790 ±

Замечаний: 0% ±

365

+1 было у Сергея - я не знаю, даты не считал - с какой там VBA считать начинает нужно погуглить. Или знатоки может подскажут.
Если что, можно так:

[vba]

Code

mydata = CDate(x / 864000000000#)

[/vba]

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Пятница, 16.11.2012, 13:23 | Сообщение № 12

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Quote (Hugo)

...с какой там VBA считать начинает?

С 100-го года

Date Хранение даты и времени Даты от 1.01.100 до 31.12.9999 Время от 00:00:00 до 23:59:59

Date — VBA использует тип Date для хранения дат и времени.
При работе с этим типом данных следует иметь ввиду, что VBA-типы Date не являются такими же типами, как в рабочих листах Excel, хотя во многом и схожи с ними.
Например, базовой датой для VBA-типа Date является 30 декабря 1899 года, а в Excel — 1 января 1900 года.
VBA-тип Date является типом последовательных дат.

VBA использует отрицательные числа для представления дат ранее базовой даты (30 декабря 1899), и положительные — для дат после базовой.
Число 0 представляет саму дату 30.12.1899.

В значении последовательной даты целая часть — это общее число дней от базовой даты.
Дробная часть (цифра справа от десятичного знака) — эти цифры обозначают время дня как часть дня.
Один час — это 1/24 дня, одна минута — 1/1440 дня, секунда — 1/86400 дня.

Можно вычитать одну дату из другой, добавлять к дате или вычитать числа для изменения ее значения.
В VBA имеется несколько встроенных процедур для отдельного извлечения года, месяца, дня, часов, минут и секунд переменной типа Date.

Quote (Hugo)

...с какой там VBA считать начинает?

С 100-го года

Date Хранение даты и времени Даты от 1.01.100 до 31.12.9999 Время от 00:00:00 до 23:59:59

Date — VBA использует тип Date для хранения дат и времени.
При работе с этим типом данных следует иметь ввиду, что VBA-типы Date не являются такими же типами, как в рабочих листах Excel, хотя во многом и схожи с ними.
Например, базовой датой для VBA-типа Date является 30 декабря 1899 года, а в Excel — 1 января 1900 года.
VBA-тип Date является типом последовательных дат.

VBA использует отрицательные числа для представления дат ранее базовой даты (30 декабря 1899), и положительные — для дат после базовой.
Число 0 представляет саму дату 30.12.1899.

В значении последовательной даты целая часть — это общее число дней от базовой даты.
Дробная часть (цифра справа от десятичного знака) — эти цифры обозначают время дня как часть дня.
Один час — это 1/24 дня, одна минута — 1/1440 дня, секунда — 1/86400 дня.

Можно вычитать одну дату из другой, добавлять к дате или вычитать числа для изменения ее значения.
В VBA имеется несколько встроенных процедур для отдельного извлечения года, месяца, дня, часов, минут и секунд переменной типа Date.Serge_007

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Сообщение

Quote (Hugo)

...с какой там VBA считать начинает?

С 100-го года

Date Хранение даты и времени Даты от 1.01.100 до 31.12.9999 Время от 00:00:00 до 23:59:59

Date — VBA использует тип Date для хранения дат и времени.
При работе с этим типом данных следует иметь ввиду, что VBA-типы Date не являются такими же типами, как в рабочих листах Excel, хотя во многом и схожи с ними.
Например, базовой датой для VBA-типа Date является 30 декабря 1899 года, а в Excel — 1 января 1900 года.
VBA-тип Date является типом последовательных дат.

VBA использует отрицательные числа для представления дат ранее базовой даты (30 декабря 1899), и положительные — для дат после базовой.
Число 0 представляет саму дату 30.12.1899.

В значении последовательной даты целая часть — это общее число дней от базовой даты.
Дробная часть (цифра справа от десятичного знака) — эти цифры обозначают время дня как часть дня.
Один час — это 1/24 дня, одна минута — 1/1440 дня, секунда — 1/86400 дня.

Можно вычитать одну дату из другой, добавлять к дате или вычитать числа для изменения ее значения.
В VBA имеется несколько встроенных процедур для отдельного извлечения года, месяца, дня, часов, минут и секунд переменной типа Date.

Автор - Serge_007
Дата добавления - 16.11.2012 в 13:23

Дата: Пятница, 16.11.2012, 13:25 | Сообщение № 13

Группа: Админы

Ранг: Местный житель

Сообщений: 19405

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

+1 всё-таки, наверное, нужно, т.к. даты начинают отсчитываться не с 01.01.1900, а с 00.01.1900
Тогда моя формула

Code

=1+A1/864000000000

дает тот же результат, что и UDF

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Пятница, 16.11.2012, 13:26 | Сообщение № 14

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

Quote (Pelena)

Остаётся разница с тестом в три дня

Quote (Pelena)

Почему +1?

Почему не учитываем високосные годы, точнее не високосные.
1900, 2100, 2200, 2300 - не високосные, а 2000 - високосный

При этом в VBA и Excel 1900 год различен. в Excel есть 29.02.1900 а в VBA нет такой даты, после 28.02.1900 следует 01.03.1900
Таким образом в Excel значению "1" (начало счета) соответствует 01.01.1900 а в VBA "1" - это 31.12.1899.
Проверить можно легко, напишите в окне Immediate:
?CDate(1)

с 01.03.1900 нумерация дат в Excel и VBA совпадают

Сообщение отредактировал MCH - Пятница, 16.11.2012, 13:27

Ответить

Дата: Пятница, 16.11.2012, 13:32 | Сообщение № 15

Группа: Админы

Ранг: Местный житель

Сообщений: 19405

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Quote (MCH)

Почему не учитываем високосные годы, точнее не високосные

Разве Excel при переводе числа в дату это не учитывает?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Дата: Пятница, 16.11.2012, 13:33 | Сообщение № 16

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

Quote (Serge_007)

С 100-го года

дате 01.01.100 соответствует число -657434
?CDate(-657434)

Ответить

Дата: Пятница, 16.11.2012, 13:36 | Сообщение № 17

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3691

Репутация: 790 ±

Замечаний: 0% ±

365

Да, где-то я читал целый рассказ про этот косяк с 29.02.1900. В общем, Билл был в курсе smile

smile

Хотя я сейчас не помню - была такая дата или нет? Но мне это и не нужно...
Но по логике событий - её небыло smile

smile

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

Дата: Пятница, 16.11.2012, 13:36 | Сообщение № 18

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Quote (MCH)

дате 01.01.100 соответствует число -657434

Quote (Serge_007)

VBA использует отрицательные числа для представления дат ранее базовой даты (30 декабря 1899), и положительные — для дат после базовой.
Число 0 представляет саму дату 30.12.1899.

ЮMoney:41001419691823 | WMR:126292472390

Ответить

Дата: Пятница, 16.11.2012, 13:39 | Сообщение № 19

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

Quote (Hugo)

где-то я читал целый рассказ про этот косяк с 29.02.1900

http://www.planetaexcel.ru/forum.php?thread_id=10915
в посте vikttur'а указана ссылка на статью

Ответить

Дата: Пятница, 16.11.2012, 13:45 | Сообщение № 20

Группа: Админы

Ранг: Старожил

Сообщений: 2004

Репутация: 752 ±

Замечаний: ±

Quote (Serge_007)

Верно

Сергей, ты правишь свои посты без отметки об исправлении.
Вначале ты написал, что точно не уверен с какой даты начинается отсчет, что возможно это надсройка Уокенбаха

Мне интересно было проверить, а в справке я этого не увидел.
Методом подбора я подобрал число -657434 которому соответствует 01.01.100, даты для числа -657435 в VBA нет
Кроме того, в твоем новом посте не указано минимальное число соответствующее 01.01.100

Сообщение отредактировал MCH - Пятница, 16.11.2012, 13:46

Ответить

Формула для преобразования числа в дату

Страница 1 из 2
1
2
»

© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!