Convert an Excel date code to a "date"

Question

Given a non-negative integer Excel-style date code, return the corresponding "date" in any reasonable form that clearly shows year, month, and "day".

Trivial, you may think. Did you notice the "scare quotes"? I used those because Excel has some quirks. Excel counts days with number 1 for January 1^st, 1900, but as if 1900 had a January 0^th and a February 29^th, so be very careful to try all test cases:

 Input → Output (example format)
     0 → 1900-01-00    Note: NOT 1899-12-31
     1 → 1900-01-01
     2 → 1900-01-02
    59 → 1900-02-28
    60 → 1900-02-29    Note: NOT 1900-03-01
    61 → 1900-03-01
   100 → 1900-04-09
  1000 → 1902-09-26
 10000 → 1927-05-18
100000 → 2173-10-14

Answer 1

Excel, 3(+7?)

=A1

with format

yyy/m/d

Pure port

Answer 2

k (kdb+ 3.5), 55 54 51 50 bytes

{$(`1900.01.00`1900.02.29,"d"$x-36526-x<60)0 60?x}

to test, paste this line in the q console:

k)-1@{$(`1900.01.00`1900.02.29,"d"$x-36526-x<60)0 60?x}'0 1 2 59 60 61 100 1000 10000 100000;

the output should be

1900.01.00
1900.01.01
1900.01.02
1900.02.28
1900.02.29
1900.03.01
1900.04.09
1902.09.26
1927.05.18
2173.10.14

{ } is a function with argument x

0 60?x index of x among 0 60 or 2 if not found

ˋ1900.01.00ˋ1900.02.29 a list of two symbols

, append to it

"d"$ converted to a date

x-36526 number of days since 1900 (instead of the default 2000)

- x<60 adjust for excel's leap error

(ˋ1900.01.00ˋ1900.02.29,"d"$x-36526-x<60)@0 60?x juxtaposition means indexing - the "@" in the middle is implicit

$ convert to string

Answer 3

Python 2, 111 bytes

from datetime import*
n=input()
print('1900-0'+'12--0209'[n>9::2],date(1900,1,1)+timedelta(n+~(n>59)))[0<n!=60]

Try it online!

-5 thanks to ngn.

Answer 4

JavaScript (ES6),  89 82  77 bytes

Saved  7  12 bytes thanks to @tsh

n=>(p=n>60?'':19)+new Date(p*400,0,n-!p||1).toJSON().slice(p/9,10-!n)+(n&&'')

Try it online!

Answer 5

Clean, 205 189 bytes

import StdEnv
a=30;b=31;c=1900;r=rem
@m=sum(take m(?c))
?n=[b,if(n>c&&(r n 4>0||r n 100<1&&r n 400>0))28 29,b,a,b,a,b,b,a,b,a,b: ?(n+1)]
$n#m=while(\m= @m<n)inc 0-1
=(c+m/12,1+r m 12,n- @m)

Try it online!

Answer 6

APL (Dyalog Classic), 31 bytes

Anonymous tacit prefix function. Returns date as [Y,M,D]

(¯3↑×-60∘≠)+3↑2⎕NQ#263,60∘>+⊢-×

Try it online!

× sign of the date code

⊢- subtract that from the argument (the date code)

60∘>+ increment if date code is above sixty

2⎕NQ#263, use that as immediate argument for "Event 263" (IDN to date)
 ^{IDN is just like Excel's date code, but without Feb 29, 1900, and the day before Jan 1, 1900 is Dec 31, 1899}

3↑ take the first three elements of that (the fourth one is day of week)

(…)+ add the following to those:

 60∘≠ 0 if date code is 60; 1 if date code is not 60

 ×- subtract that from the sign of the date code

 ¯3↑ take the last three elements (there is only one) padding with (two) zeros

developed together with @Adám in chat

Answer 7

Japt, 43 bytes

Ended up with a part port of Arnauld's solution.

Output is in yyyy-m-d format.

?U-#<?Ð#¾0TUaU>#<)s7:"1900-2-29":"1900-1-0"

Try it online or test 0-100

Answer 8

C# (.NET Core), 186 185 bytes

using System;class P{static void Main(){var i=int.Parse(Console.ReadLine());Console.Write((i==0|i==60)?$"1900-{i%59+1}-{i%31}":DateTime.FromOADate(i+(i<60?1:0)).ToString("yyyy-M-d"));}}

Try it online!

---

-1 byte by replacing OR operator(||) with binary OR operator(|).

Answer 9

Perl 6, 81 bytes

{$_??$_-60??Date.new-from-daycount($_+15018+(60>$_))!!'1900-02-29'!!'1900-01-00'}

Try it online!

Answer 10

T-SQL, 141 95 94 bytes

SELECT IIF(n=0,'1/0/1900',IIF(n=60,'2/29/1900',
FORMAT(DATEADD(d,n,-IIF(n<60,1,2)),'d')))FROM i

Line break is for readability only.

Input is taken via pre-existing table i with integer field n, per our IO standards.

SQL uses a similar (but corrected) 1-1-1900 starting point for its internal date format, so I only have to offset it by 1 or 2 days in the DATEADD function.

SQL can't output a column containing a mix of date and character values, so I can't leave off the FORMAT command (since it would then try to convert 1/0/1900 to a date, which is of course invalid).

What's nice about SQL is that I can load up all the input values into the table and run them all at once. My (US) locality defaults to a m/d/yyyy date format:

n       output
0       1/0/1900
1       1/1/1900
2       1/2/1900
59      2/28/1900
60      2/29/1900
61      3/1/1900
100     4/9/1900
1000    9/26/1902
10000   5/18/1927
43432   11/28/2018
100000  10/14/2173

EDIT: Saved 46 bytes by changing to a nested IIF() instead of the much more verbose CASE WHEN.

EDIT 2: Saved another byte by moving the - in front of the IIF.