Convert an Excel date code to a “date”











up vote
12
down vote

favorite
1












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









share|improve this question




















  • 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

















up vote
12
down vote

favorite
1












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









share|improve this question




















  • 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















up vote
12
down vote

favorite
1









up vote
12
down vote

favorite
1






1





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









share|improve this 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 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






code-golf date conversion






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 28 at 12:58









Glorfindel

131118




131118










asked Nov 27 at 22:13









Adám

28.4k269187




28.4k269187








  • 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
















  • 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










1




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




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




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




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




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






@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




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






@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




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






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






active

oldest

votes

















up vote
12
down vote













Excel, 3(+7?)



=A1


with format



yyy/m/d


Pure port






share|improve this answer























  • 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


















up vote
6
down vote













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






share|improve this answer



















  • 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


















up vote
3
down vote














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.






share|improve this answer























  • 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


















up vote
3
down vote













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!






share|improve this answer























  • 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


















up vote
2
down vote














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!






share|improve this answer



















  • 1




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


















up vote
1
down vote













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






share|improve this answer






























    up vote
    1
    down vote














    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!






    share|improve this answer




























      up vote
      1
      down vote














      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






      share|improve this answer






























        up vote
        0
        down vote














        Perl 6, 81 bytes





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


        Try it online!






        share|improve this answer




























          up vote
          0
          down vote













          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.






          share|improve this answer























            Your Answer





            StackExchange.ifUsing("editor", function () {
            return StackExchange.using("mathjaxEditing", function () {
            StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
            StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
            });
            });
            }, "mathjax-editing");

            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "200"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodegolf.stackexchange.com%2fquestions%2f176647%2fconvert-an-excel-date-code-to-a-date%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            10 Answers
            10






            active

            oldest

            votes








            10 Answers
            10






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes








            up vote
            12
            down vote













            Excel, 3(+7?)



            =A1


            with format



            yyy/m/d


            Pure port






            share|improve this answer























            • 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















            up vote
            12
            down vote













            Excel, 3(+7?)



            =A1


            with format



            yyy/m/d


            Pure port






            share|improve this answer























            • 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













            up vote
            12
            down vote










            up vote
            12
            down vote









            Excel, 3(+7?)



            =A1


            with format



            yyy/m/d


            Pure port






            share|improve this answer














            Excel, 3(+7?)



            =A1


            with format



            yyy/m/d


            Pure port







            share|improve this answer














            share|improve this answer



            share|improve this answer








            answered Nov 27 at 23:02


























            community wiki





            l4m2













            • 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


















            • 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
















            The output format may of course vary according to your locale.
            – Adám
            Nov 27 at 23:03




            The output format may of course vary according to your locale.
            – Adám
            Nov 27 at 23:03




            1




            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




            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






            @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




            @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










            up vote
            6
            down vote













            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






            share|improve this answer



















            • 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















            up vote
            6
            down vote













            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






            share|improve this answer



















            • 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













            up vote
            6
            down vote










            up vote
            6
            down vote









            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






            share|improve this answer














            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







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Nov 28 at 9:58

























            answered Nov 27 at 23:04









            ngn

            6,53312459




            6,53312459








            • 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














            • 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








            1




            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






            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




            @zgrep You should post since versions of K basically are entirely dissimilar languages.
            – Adám
            Nov 28 at 16:59










            up vote
            3
            down vote














            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.






            share|improve this answer























            • 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















            up vote
            3
            down vote














            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.






            share|improve this answer























            • 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













            up vote
            3
            down vote










            up vote
            3
            down vote










            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.






            share|improve this answer















            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.







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Nov 28 at 13:16

























            answered Nov 27 at 22:52









            Erik the Outgolfer

            30.9k429102




            30.9k429102












            • 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


















            • 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
















            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




            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










            up vote
            3
            down vote













            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!






            share|improve this answer























            • 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















            up vote
            3
            down vote













            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!






            share|improve this answer























            • 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













            up vote
            3
            down vote










            up vote
            3
            down vote









            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!






            share|improve this answer














            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!







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Nov 29 at 8:32

























            answered Nov 28 at 7:39









            Arnauld

            70.3k686295




            70.3k686295












            • 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


















            • 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
















            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




            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




            @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




            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




            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




            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




            Does it need to be run in GMT0/-x?
            – l4m2
            Nov 29 at 7:37










            up vote
            2
            down vote














            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!






            share|improve this answer



















            • 1




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















            up vote
            2
            down vote














            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!






            share|improve this answer



















            • 1




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













            up vote
            2
            down vote










            up vote
            2
            down vote










            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!






            share|improve this answer















            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!







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Nov 29 at 6:31

























            answered Nov 29 at 5:55









            Οurous

            6,04311032




            6,04311032








            • 1




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














            • 1




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








            1




            1




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




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










            up vote
            1
            down vote













            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






            share|improve this answer



























              up vote
              1
              down vote













              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






              share|improve this answer

























                up vote
                1
                down vote










                up vote
                1
                down vote









                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






                share|improve this answer














                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







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Nov 28 at 17:14

























                answered Nov 28 at 14:40









                Shaggy

                18.4k21663




                18.4k21663






















                    up vote
                    1
                    down vote














                    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!






                    share|improve this answer

























                      up vote
                      1
                      down vote














                      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!






                      share|improve this answer























                        up vote
                        1
                        down vote










                        up vote
                        1
                        down vote










                        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!






                        share|improve this answer













                        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!







                        share|improve this answer












                        share|improve this answer



                        share|improve this answer










                        answered Nov 29 at 8:03









                        cobaltp

                        3417




                        3417






















                            up vote
                            1
                            down vote














                            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






                            share|improve this answer



























                              up vote
                              1
                              down vote














                              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






                              share|improve this answer

























                                up vote
                                1
                                down vote










                                up vote
                                1
                                down vote










                                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






                                share|improve this answer















                                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







                                share|improve this answer














                                share|improve this answer



                                share|improve this answer








                                edited Nov 29 at 16:26









                                Adám

                                28.4k269187




                                28.4k269187










                                answered Nov 29 at 15:45









                                ngn

                                6,53312459




                                6,53312459






















                                    up vote
                                    0
                                    down vote














                                    Perl 6, 81 bytes





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


                                    Try it online!






                                    share|improve this answer

























                                      up vote
                                      0
                                      down vote














                                      Perl 6, 81 bytes





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


                                      Try it online!






                                      share|improve this answer























                                        up vote
                                        0
                                        down vote










                                        up vote
                                        0
                                        down vote










                                        Perl 6, 81 bytes





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


                                        Try it online!






                                        share|improve this answer













                                        Perl 6, 81 bytes





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


                                        Try it online!







                                        share|improve this answer












                                        share|improve this answer



                                        share|improve this answer










                                        answered Nov 28 at 11:06









                                        nwellnhof

                                        6,3131125




                                        6,3131125






















                                            up vote
                                            0
                                            down vote













                                            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.






                                            share|improve this answer



























                                              up vote
                                              0
                                              down vote













                                              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.






                                              share|improve this answer

























                                                up vote
                                                0
                                                down vote










                                                up vote
                                                0
                                                down vote









                                                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.






                                                share|improve this answer














                                                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.







                                                share|improve this answer














                                                share|improve this answer



                                                share|improve this answer








                                                edited Nov 28 at 17:46

























                                                answered Nov 28 at 15:45









                                                BradC

                                                3,609523




                                                3,609523






























                                                    draft saved

                                                    draft discarded




















































                                                    If this is an answer to a challenge…




                                                    • …Be sure to follow the challenge specification. However, please refrain from exploiting obvious loopholes. Answers abusing any of the standard loopholes are considered invalid. If you think a specification is unclear or underspecified, comment on the question instead.


                                                    • …Try to optimize your score. For instance, answers to code-golf challenges should attempt to be as short as possible. You can always include a readable version of the code in addition to the competitive one.
                                                      Explanations of your answer make it more interesting to read and are very much encouraged.


                                                    • …Include a short header which indicates the language(s) of your code and its score, as defined by the challenge.



                                                    More generally…




                                                    • …Please make sure to answer the question and provide sufficient detail.


                                                    • …Avoid asking for help, clarification or responding to other answers (use comments instead).






                                                    Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                                                    Please pay close attention to the following guidance:


                                                    • Please be sure to answer the question. Provide details and share your research!

                                                    But avoid



                                                    • Asking for help, clarification, or responding to other answers.

                                                    • Making statements based on opinion; back them up with references or personal experience.


                                                    To learn more, see our tips on writing great answers.




                                                    draft saved


                                                    draft discarded














                                                    StackExchange.ready(
                                                    function () {
                                                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodegolf.stackexchange.com%2fquestions%2f176647%2fconvert-an-excel-date-code-to-a-date%23new-answer', 'question_page');
                                                    }
                                                    );

                                                    Post as a guest















                                                    Required, but never shown





















































                                                    Required, but never shown














                                                    Required, but never shown












                                                    Required, but never shown







                                                    Required, but never shown

































                                                    Required, but never shown














                                                    Required, but never shown












                                                    Required, but never shown







                                                    Required, but never shown







                                                    Popular posts from this blog

                                                    數位音樂下載

                                                    When can things happen in Etherscan, such as the picture below?

                                                    格利澤436b