Даты в Excel
От: fortnum  
Дата: 24.06.22 14:26
Оценка:
Помогите разобраться, что происходит, зареверсить алгоритм, так сказать.
Берем (1) Google Sheets и (2) Excel. В первом столбце проставляем ряд 5, 10, 15, 20, 25, и т.д. Это будет количество секунд в сутках, прошедших с начала дня.
В ячейках второго столбца делим первый столбец на (24*3600), то есть на количество секунд в сутках, ну и еще умножим на 0.1 — растянем шкалу — чтоб не 2 записи на каждые 10 секунд приходились, а 2 записи на каждую секунду.
Ячейки третьего столбца равны ячейкам второго — то же число. Только надо назначить на них форматирование "чч:мм:сс".

Вот, что получается у Google Sheets — первое значение каждой секунды, ровно её половина, попадает на 0.5 секунды и округляется форматтером до целой секунды. Второе значение попадает на целое значение той же секунды, и равно ей — все ясно-понятно:
  Скрытый текст
005    0.00000578703703704    00:00:01 
010    0.00001157407407407    00:00:01 
015    0.00001736111111111    00:00:02 
020    0.00002314814814815    00:00:02 
025    0.00002893518518519    00:00:03 
030    0.00003472222222222    00:00:03 
035    0.00004050925925926    00:00:04 
040    0.00004629629629630    00:00:04 
045    0.00005208333333333    00:00:05 
050    0.00005787037037037    00:00:05 
055    0.00006365740740741    00:00:06 
060    0.00006944444444444    00:00:06 
065    0.00007523148148148    00:00:07 
070    0.00008101851851852    00:00:07 
075    0.00008680555555556    00:00:08 
080    0.00009259259259259    00:00:08 
085    0.00009837962962963    00:00:09 
090    0.00010416666666667    00:00:09 
095    0.00010995370370370    00:00:10 
100    0.00011574074074074    00:00:10 
105    0.00012152777777778    00:00:11 
110    0.00012731481481482    00:00:11 
115    0.00013310185185185    00:00:12 
120    0.00013888888888889    00:00:12 
125    0.00014467592592593    00:00:13 
130    0.00015046296296296    00:00:13 
135    0.00015625000000000    00:00:14 
140    0.00016203703703704    00:00:14 
145    0.00016782407407407    00:00:15 
150    0.00017361111111111    00:00:15 
155    0.00017939814814815    00:00:16 
160    0.00018518518518519    00:00:16 
165    0.00019097222222222    00:00:17 
170    0.00019675925925926    00:00:17 
175    0.00020254629629630    00:00:18 
180    0.00020833333333333    00:00:18 
185    0.00021412037037037    00:00:19 
190    0.00021990740740741    00:00:19 
195    0.00022569444444444    00:00:20 
200    0.00023148148148148    00:00:20 
205    0.00023726851851852    00:00:21 
210    0.00024305555555556    00:00:21 
215    0.00024884259259259    00:00:22 
220    0.00025462962962963    00:00:22 
225    0.00026041666666667    00:00:23 
230    0.00026620370370370    00:00:23 
235    0.00027199074074074    00:00:24 
240    0.00027777777777778    00:00:24 
245    0.00028356481481482    00:00:25 
250    0.00028935185185185    00:00:25 
255    0.00029513888888889    00:00:26 
260    0.00030092592592593    00:00:26 
265    0.00030671296296296    00:00:27 
270    0.00031250000000000    00:00:27 
275    0.00031828703703704    00:00:28 
280    0.00032407407407407    00:00:28 
285    0.00032986111111111    00:00:29 
290    0.00033564814814815    00:00:29 
295    0.00034143518518519    00:00:30 
300    0.00034722222222222    00:00:30 
305    0.00035300925925926    00:00:31 
310    0.00035879629629630    00:00:31 
315    0.00036458333333333    00:00:32 
320    0.00037037037037037    00:00:32 
325    0.00037615740740741    00:00:33 
330    0.00038194444444444    00:00:33 
335    0.00038773148148148    00:00:34 
340    0.00039351851851852    00:00:34 
345    0.00039930555555556    00:00:35 
350    0.00040509259259259    00:00:35 
355    0.00041087962962963    00:00:36 
360    0.00041666666666667    00:00:36 
365    0.00042245370370370    00:00:37 
370    0.00042824074074074    00:00:37 
375    0.00043402777777778    00:00:38 
380    0.00043981481481482    00:00:38 
385    0.00044560185185185    00:00:39 
390    0.00045138888888889    00:00:39 
395    0.00045717592592593    00:00:40 
400    0.00046296296296296    00:00:40 
405    0.00046875000000000    00:00:41 
410    0.00047453703703704    00:00:41 
415    0.00048032407407407    00:00:42 
420    0.00048611111111111    00:00:42 
425    0.00049189814814815    00:00:43 
430    0.00049768518518519    00:00:43 
435    0.00050347222222222    00:00:44 
440    0.00050925925925926    00:00:44 
445    0.00051504629629630    00:00:45 
450    0.00052083333333333    00:00:45 
455    0.00052662037037037    00:00:46 
460    0.00053240740740741    00:00:46 
465    0.00053819444444444    00:00:47 
470    0.00054398148148148    00:00:47 
475    0.00054976851851852    00:00:48 
480    0.00055555555555556    00:00:48 
485    0.00056134259259259    00:00:49 
490    0.00056712962962963    00:00:49 
495    0.00057291666666667    00:00:50 
500    0.00057870370370370    00:00:50 
505    0.00058449074074074    00:00:51 
510    0.00059027777777778    00:00:51 
515    0.00059606481481482    00:00:52 
520    0.00060185185185185    00:00:52 
525    0.00060763888888889    00:00:53 
530    0.00061342592592593    00:00:53 
535    0.00061921296296296    00:00:54 
540    0.00062500000000000    00:00:54 
545    0.00063078703703704    00:00:55 
550    0.00063657407407407    00:00:55 
555    0.00064236111111111    00:00:56 
560    0.00064814814814815    00:00:56 
565    0.00065393518518519    00:00:57 
570    0.00065972222222222    00:00:57 
575    0.00066550925925926    00:00:58 
580    0.00067129629629630    00:00:58 
585    0.00067708333333333    00:00:59 
590    0.00068287037037037    00:00:59 
595    0.00068865740740741    00:01:00 
600    0.00069444444444444    00:01:00 
605    0.00070023148148148    00:01:01 
610    0.00070601851851852    00:01:01 
615    0.00071180555555556    00:01:02 
620    0.00071759259259259    00:01:02 
625    0.00072337962962963    00:01:03 
630    0.00072916666666667    00:01:03 
635    0.00073495370370370    00:01:04 
640    0.00074074074074074    00:01:04 
645    0.00074652777777778    00:01:05 
650    0.00075231481481482    00:01:05 
655    0.00075810185185185    00:01:06 
660    0.00076388888888889    00:01:06 
665    0.00076967592592593    00:01:07 
670    0.00077546296296296    00:01:07 
675    0.00078125000000000    00:01:08 
680    0.00078703703703704    00:01:08 
685    0.00079282407407407    00:01:09 
690    0.00079861111111111    00:01:09 
695    0.00080439814814815    00:01:10 
700    0.00081018518518519    00:01:10 
705    0.00081597222222222    00:01:11 
710    0.00082175925925926    00:01:11 
715    0.00082754629629630    00:01:12 
720    0.00083333333333333    00:01:12 
725    0.00083912037037037    00:01:13 
730    0.00084490740740741    00:01:13 
735    0.00085069444444444    00:01:14 
740    0.00085648148148148    00:01:14 
745    0.00086226851851852    00:01:15 
750    0.00086805555555556    00:01:15 
755    0.00087384259259259    00:01:16 
760    0.00087962962962963    00:01:16


А вот что получается у Excel:
  Скрытый текст
005    0,00000578703703703704    00:00:01
010    0,00001157407407407410    00:00:01
015    0,00001736111111111110    00:00:02
020    0,00002314814814814820    00:00:02
025    0,00002893518518518520    00:00:02 # должно быть 3
030    0,00003472222222222220    00:00:03
035    0,00004050925925925930    00:00:04
040    0,00004629629629629630    00:00:04
045    0,00005208333333333330    00:00:05
050    0,00005787037037037040    00:00:05
055    0,00006365740740740740    00:00:06
060    0,00006944444444444440    00:00:06
065    0,00007523148148148150    00:00:07
070    0,00008101851851851850    00:00:07
075    0,00008680555555555560    00:00:08
080    0,00009259259259259260    00:00:08
085    0,00009837962962962960    00:00:09
090    0,00010416666666666700    00:00:09
095    0,00010995370370370400    00:00:10
100    0,00011574074074074100    00:00:10
105    0,00012152777777777800    00:00:11
110    0,00012731481481481500    00:00:11
115    0,00013310185185185200    00:00:11 # должно быть 12
120    0,00013888888888888900    00:00:12
125    0,00014467592592592600    00:00:12 # должно быть 13
130    0,00015046296296296300    00:00:13
135    0,00015625000000000000    00:00:14
140    0,00016203703703703700    00:00:14
145    0,00016782407407407400    00:00:15
150    0,00017361111111111100    00:00:15
155    0,00017939814814814800    00:00:16
160    0,00018518518518518500    00:00:16
165    0,00019097222222222200    00:00:17
170    0,00019675925925925900    00:00:17
175    0,00020254629629629600    00:00:17 # должно быть 18
180    0,00020833333333333300    00:00:18
185    0,00021412037037037000    00:00:19
190    0,00021990740740740700    00:00:19
195    0,00022569444444444400    00:00:20
200    0,00023148148148148100    00:00:20
205    0,00023726851851851900    00:00:21
210    0,00024305555555555600    00:00:21
215    0,00024884259259259300    00:00:22
220    0,00025462962962963000    00:00:22
225    0,00026041666666666700    00:00:23
230    0,00026620370370370400    00:00:23
235    0,00027199074074074100    00:00:24
240    0,00027777777777777800    00:00:24
245    0,00028356481481481500    00:00:25
250    0,00028935185185185200    00:00:25
255    0,00029513888888888900    00:00:26
260    0,00030092592592592600    00:00:26
265    0,00030671296296296300    00:00:27
270    0,00031250000000000000    00:00:27
275    0,00031828703703703700    00:00:28
280    0,00032407407407407400    00:00:28
285    0,00032986111111111100    00:00:29
290    0,00033564814814814800    00:00:29
295    0,00034143518518518500    00:00:30
300    0,00034722222222222200    00:00:30
305    0,00035300925925925900    00:00:31
310    0,00035879629629629600    00:00:31
315    0,00036458333333333300    00:00:32
320    0,00037037037037037000    00:00:32
325    0,00037615740740740700    00:00:33
330    0,00038194444444444400    00:00:33
335    0,00038773148148148200    00:00:34
340    0,00039351851851851900    00:00:34
345    0,00039930555555555600    00:00:35
350    0,00040509259259259300    00:00:35
355    0,00041087962962963000    00:00:36
360    0,00041666666666666700    00:00:36
365    0,00042245370370370400    00:00:37
370    0,00042824074074074100    00:00:37
375    0,00043402777777777800    00:00:38
380    0,00043981481481481500    00:00:38
385    0,00044560185185185200    00:00:39
390    0,00045138888888888900    00:00:39
395    0,00045717592592592600    00:00:40
400    0,00046296296296296300    00:00:40
405    0,00046875000000000000    00:00:41
410    0,00047453703703703700    00:00:41
415    0,00048032407407407400    00:00:42
420    0,00048611111111111100    00:00:42
425    0,00049189814814814800    00:00:43
430    0,00049768518518518500    00:00:43
435    0,00050347222222222200    00:00:44
440    0,00050925925925925900    00:00:44
445    0,00051504629629629600    00:00:45
450    0,00052083333333333300    00:00:45
455    0,00052662037037037000    00:00:46
460    0,00053240740740740700    00:00:46
465    0,00053819444444444400    00:00:47
470    0,00054398148148148200    00:00:47
475    0,00054976851851851900    00:00:47 # должно быть 48
480    0,00055555555555555600    00:00:48
485    0,00056134259259259300    00:00:49
490    0,00056712962962963000    00:00:49
495    0,00057291666666666700    00:00:49 # должно быть 50
500    0,00057870370370370400    00:00:50
505    0,00058449074074074100    00:00:51
510    0,00059027777777777800    00:00:51
515    0,00059606481481481500    00:00:52
520    0,00060185185185185200    00:00:52
525    0,00060763888888888900    00:00:53
530    0,00061342592592592600    00:00:53
535    0,00061921296296296300    00:00:53 # должно быть 54
540    0,00062500000000000000    00:00:54
545    0,00063078703703703700    00:00:55
550    0,00063657407407407400    00:00:55
555    0,00064236111111111100    00:00:56
560    0,00064814814814814800    00:00:56
565    0,00065393518518518500    00:00:57
570    0,00065972222222222200    00:00:57
575    0,00066550925925925900    00:00:58
580    0,00067129629629629600    00:00:58
585    0,00067708333333333300    00:00:59
590    0,00068287037037037000    00:00:59
595    0,00068865740740740700    00:01:00
600    0,00069444444444444400    00:01:00
605    0,00070023148148148100    00:01:00 # должно быть 1:01
610    0,00070601851851851900    00:01:01
615    0,00071180555555555600    00:01:01 # должно быть 1:02
620    0,00071759259259259300    00:01:02
625    0,00072337962962963000    00:01:03
630    0,00072916666666666700    00:01:03
635    0,00073495370370370400    00:01:04
640    0,00074074074074074100    00:01:04
645    0,00074652777777777800    00:01:04 # должно быть 1:05
650    0,00075231481481481500    00:01:05
655    0,00075810185185185200    00:01:06
660    0,00076388888888888900    00:01:06
665    0,00076967592592592600    00:01:06 # должно быть 1:07
670    0,00077546296296296300    00:01:07
675    0,00078125000000000000    00:01:07 # должно быть 1:08
680    0,00078703703703703700    00:01:08
685    0,00079282407407407400    00:01:09
690    0,00079861111111111100    00:01:09
695    0,00080439814814814800    00:01:10
700    0,00081018518518518500    00:01:10
705    0,00081597222222222200    00:01:11
710    0,00082175925925925900    00:01:11
715    0,00082754629629629600    00:01:11 # должно быть 1:12
720    0,00083333333333333300    00:01:12
725    0,00083912037037037000    00:01:13
730    0,00084490740740740700    00:01:13
735    0,00085069444444444400    00:01:13 # должно быть 1:14
740    0,00085648148148148200    00:01:14
745    0,00086226851851851900    00:01:15
750    0,00086805555555555600    00:01:15
755    0,00087384259259259300    00:01:15 # должно быть 1:16
760    0,00087962962962963000    00:01:16
765    0,00088541666666666700    00:01:16 # должно быть 1:17
770    0,00089120370370370400    00:01:17
775    0,00089699074074074100    00:01:18
780    0,00090277777777777800    00:01:18
785    0,00090856481481481500    00:01:19
790    0,00091435185185185200    00:01:19
795    0,00092013888888888900    00:01:20
800    0,00092592592592592600    00:01:20


Как Excel такое умудряется сделать? Так же и OleDb провайдер ACE/Jet себя ведет.

Мне просто надо повторить этот алгоритм форматирования строк. Есть идеи, или может кто знает?


PS1. Дробная часть числа во втором столбце — это часть суток, время, иными словами. То есть 0.25 — это 24*0.25 = 6 часов утра, и т.д.

PS2. Набросал программку на C#, чтобы с double и с decimal поэкспериментировать. У ряда с double числа совпадают с тем, что у Excel. А вот секунды как подстроить, чтобы точь-в-точь генерились, пока не понимаю. Вот, может кому пригодится:

  Скрытый текст
static void Main()
{
    var nn1 = Enumerable.Range(1, 100).Select(x => x * 5.0m);
    var nn2 = Enumerable.Range(1, 100).Select(x => x * 5.0);

    var vv1 = nn1.Select(x => (x, x * 1.0m / (24 * 3600) * 0.1m)).ToArray();
    var vv2 = nn2.Select(x => (x, x * 1.0 / (24 * 3600) * 0.1)).ToArray();

    var vt1 = vv1.Select(x => (x.Item1, x.Item2, TimeSpan.FromDays((double)x.Item2)));
    var vt2 = vv2.Select(x => (x.Item1, x.Item2, TimeSpan.FromDays((double)x.Item2)));

    var vs1 = vt1.Select(x => $"{x.Item1:000}\t{x.Item2:0.00000000000000000000}\t{x.Item3.TotalSeconds}");
    var vs2 = vt2.Select(x => $"{x.Item1:000}\t{x.Item2:0.00000000000000000000}\t{x.Item3.TotalSeconds}");

    var str1 = string.Join(Environment.NewLine, vs1);
    var str2 = string.Join(Environment.NewLine, vs2);
}


str1
  Скрытый текст
005    0,00000578703703703704    0,5
010    0,00001157407407407407    1
015    0,00001736111111111111    1,5
020    0,00002314814814814815    2
025    0,00002893518518518519    2,5
030    0,00003472222222222222    3
035    0,00004050925925925926    3,5
040    0,00004629629629629630    4
045    0,00005208333333333333    4,5
050    0,00005787037037037037    5
055    0,00006365740740740741    5,5
060    0,00006944444444444444    6
065    0,00007523148148148148    6,5
070    0,00008101851851851852    7
075    0,00008680555555555556    7,5
080    0,00009259259259259259    8
085    0,00009837962962962963    8,5
090    0,00010416666666666667    9
095    0,00010995370370370370    9,5
100    0,00011574074074074074    10
105    0,00012152777777777778    10,5
110    0,00012731481481481481    11
115    0,00013310185185185185    11,5
120    0,00013888888888888889    12
125    0,00014467592592592593    12,5
130    0,00015046296296296296    13
135    0,00015625000000000000    13,5


str2
  Скрытый текст
005    0,00000578703703703704    0,5
010    0,00001157407407407410    1
015    0,00001736111111111110    1,5
020    0,00002314814814814820    2
025    0,00002893518518518520    2,5
030    0,00003472222222222220    3
035    0,00004050925925925930    3,5
040    0,00004629629629629630    4
045    0,00005208333333333330    4,5
050    0,00005787037037037040    5
055    0,00006365740740740740    5,5
060    0,00006944444444444440    6
065    0,00007523148148148150    6,5
070    0,00008101851851851850    7
075    0,00008680555555555560    7,5
080    0,00009259259259259260    8
085    0,00009837962962962960    8,5
090    0,00010416666666666700    9
095    0,00010995370370370400    9,5
100    0,00011574074074074100    10
105    0,00012152777777777800    10,5
110    0,00012731481481481500    11
115    0,00013310185185185200    11,5
120    0,00013888888888888900    12
125    0,00014467592592592600    12,5
130    0,00015046296296296300    13
135    0,00015625000000000000    13,5
Отредактировано 24.06.2022 14:29 fortnum . Предыдущая версия .
 
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.