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 1st, 1900, but as if 1900 had a January 0th and a February 29th, 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
  • 1
    Does every year have a 0th of January and 29th of February or is 1900 the only anomaly? – Shaggy Nov 27 at 22:24
  • 3
    1900 is the anomaly. Excel treats leap years correctly except for 1900 (which is not a leap year). But that was for compatibility with Lotus 1-2-3, where the bug originated. – Rick Hitchcock Nov 27 at 22:31
  • 2
    @RickHitchcock Apparently, the Lotus 1-2-3 devs did it to save on leap year code, such that the rule simply became every fourth year. With good reason too; 1900 was far in the past, and 2100 is, well, in a while. – Adám Nov 27 at 22:41
  • 2
    @RickHitchcock It may very well be that the original Lotus 1-2-3 couldn't handle Y2K, and so Microsoft decided to mimic that one issue, but otherwise stay right. Btw, the legacy lives on: .NET's OADate has epoch 1899-12-30 so that it will line up with Excel on all but the first two months of 1900, however this necessitates the DayOfWeek method because the original epoch, 1899-12-30 (or the fictive 1900-01-00) was chosen such that the weekday simply was the mod-7 of the day number, but that won't work with 1899-12-30. – Adám Nov 27 at 23:09
  • 2
    Here's the story behind the "why" about Excel dates: Joel on Software: My First BillG Review. Informative (and entertaining) read. – BradC Nov 28 at 16:31

10 Answers 10

Excel, 3(+7?)

=A1

with format

yyy/m/d

Pure port

  • The output format may of course vary according to your locale. – Adám Nov 27 at 23:03
  • 1
    This only works on Excel for Windows. Excel for a Mac has a number system that starts with dates in 1904, not 1900. It will not report a date for any year in 1900, which are part of the test cases. You may want to specify that this is Excel for Windows. – Keeta Nov 28 at 15:37
  • @Keeta For this to work on Excel for Mac, simply uncheck "Use 1904 date system" in preferences. – BradC Nov 28 at 19:45
  • @BradC Although true, any change to the default configuration of a program is perfectly fine BUT must be included in the answer. I comment this as a point to improve the answer. I would say either switch the name of it to Excel for Windows, add the caveat, or switch to OpenOffice Calc (or similar, since they purposefully included the bug, too). codegolf.meta.stackexchange.com/questions/10037/… – Keeta Nov 28 at 20:28

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

  • 1
    For a different version of k (k5/k6, I think), {$[x;$`d$x-65746;"1900.01.00"]} seems to work. I assume something overflows somewhere for 100000. – zgrep Nov 28 at 12:53
  • @zgrep You should post since versions of K basically are entirely dissimilar languages. – Adám Nov 28 at 16:59

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.

  • Note: I'm pretty sure this will turn out to be longer as a lambda, since the format of the result shouldn't vary. – Erik the Outgolfer Nov 27 at 22:55

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!

  • n=>n?n-60?new Date(1900,0,n-(n>60)).toJSON().slice(0,10):'1900-02-29':'1900-01-00' – tsh Nov 28 at 10:52
  • @tsh That's much better indeed. Thanks. (Also, I wonder if this approach could somehow be golfed.) – Arnauld Nov 28 at 11:12
  • I just find out new Date(0,0,1) is same as new Date(1900,0,1). So remove 190 saves 3 bytes. And... – tsh Nov 29 at 6:24
  • 2
    77 bytes: n=>(p=n>60?'':19)+new Date(p*400,0,n-!p||1).toJSON().slice(p/9,10-!n)+(n&&'') – tsh Nov 29 at 6:25
  • Does it need to be run in GMT0/-x? – l4m2 Nov 29 at 7:37

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!

  • 1
    First answer that doesn't use built-in date handling. Nice! – Adám Nov 29 at 6:07

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

C# (.NET Core), 186 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!

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

Perl 6, 81 bytes

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

Try it online!

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.