Convert an Excel date code to a “date”











up vote
3
down vote

favorite












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 Januray 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
    4 hours ago






  • 1




    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
    4 hours ago










  • @Shaggy Only 1900 is anomalous.
    – Adám
    4 hours ago






  • 1




    @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
    4 hours ago








  • 1




    @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
    3 hours ago

















up vote
3
down vote

favorite












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 Januray 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
    4 hours ago






  • 1




    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
    4 hours ago










  • @Shaggy Only 1900 is anomalous.
    – Adám
    4 hours ago






  • 1




    @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
    4 hours ago








  • 1




    @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
    3 hours ago















up vote
3
down vote

favorite









up vote
3
down vote

favorite











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 Januray 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 Januray 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 3 hours ago

























asked 4 hours ago









Adám

28.3k269186




28.3k269186








  • 1




    Does every year have a 0th of January and 29th of February or is 1900 the only anomaly?
    – Shaggy
    4 hours ago






  • 1




    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
    4 hours ago










  • @Shaggy Only 1900 is anomalous.
    – Adám
    4 hours ago






  • 1




    @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
    4 hours ago








  • 1




    @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
    3 hours ago
















  • 1




    Does every year have a 0th of January and 29th of February or is 1900 the only anomaly?
    – Shaggy
    4 hours ago






  • 1




    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
    4 hours ago










  • @Shaggy Only 1900 is anomalous.
    – Adám
    4 hours ago






  • 1




    @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
    4 hours ago








  • 1




    @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
    3 hours ago










1




1




Does every year have a 0th of January and 29th of February or is 1900 the only anomaly?
– Shaggy
4 hours ago




Does every year have a 0th of January and 29th of February or is 1900 the only anomaly?
– Shaggy
4 hours ago




1




1




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
4 hours ago




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
4 hours ago












@Shaggy Only 1900 is anomalous.
– Adám
4 hours ago




@Shaggy Only 1900 is anomalous.
– Adám
4 hours ago




1




1




@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
4 hours ago






@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
4 hours ago






1




1




@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
3 hours ago






@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
3 hours ago












3 Answers
3






active

oldest

votes

















up vote
1
down vote














Python 2, 116 bytes





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


Try it online!






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
    3 hours ago










  • days= is not required
    – ngn
    2 hours ago


















up vote
1
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
    3 hours ago


















up vote
1
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



,"d"$x-36526-x<60 append to it a date x days after 1900.01.00, adjusted 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























    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

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    1
    down vote














    Python 2, 116 bytes





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


    Try it online!






    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
      3 hours ago










    • days= is not required
      – ngn
      2 hours ago















    up vote
    1
    down vote














    Python 2, 116 bytes





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


    Try it online!






    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
      3 hours ago










    • days= is not required
      – ngn
      2 hours ago













    up vote
    1
    down vote










    up vote
    1
    down vote










    Python 2, 116 bytes





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


    Try it online!






    share|improve this answer













    Python 2, 116 bytes





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


    Try it online!







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered 3 hours ago









    Erik the Outgolfer

    30.7k429102




    30.7k429102












    • 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
      3 hours ago










    • days= is not required
      – ngn
      2 hours ago


















    • 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
      3 hours ago










    • days= is not required
      – ngn
      2 hours ago
















    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
    3 hours ago




    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
    3 hours ago












    days= is not required
    – ngn
    2 hours ago




    days= is not required
    – ngn
    2 hours ago










    up vote
    1
    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
      3 hours ago















    up vote
    1
    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
      3 hours ago













    up vote
    1
    down vote










    up vote
    1
    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 3 hours ago


























    community wiki





    l4m2













    • The output format may of course vary according to your locale.
      – Adám
      3 hours ago


















    • The output format may of course vary according to your locale.
      – Adám
      3 hours ago
















    The output format may of course vary according to your locale.
    – Adám
    3 hours ago




    The output format may of course vary according to your locale.
    – Adám
    3 hours ago










    up vote
    1
    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



    ,"d"$x-36526-x<60 append to it a date x days after 1900.01.00, adjusted 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



























      up vote
      1
      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



      ,"d"$x-36526-x<60 append to it a date x days after 1900.01.00, adjusted 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

























        up vote
        1
        down vote










        up vote
        1
        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



        ,"d"$x-36526-x<60 append to it a date x days after 1900.01.00, adjusted 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



        ,"d"$x-36526-x<60 append to it a date x days after 1900.01.00, adjusted 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 3 hours ago

























        answered 3 hours ago









        ngn

        6,45312459




        6,45312459






























             

            draft saved


            draft discarded



















































             


            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

            404 Error Contact Form 7 ajax form submitting

            How to know if a Active Directory user can login interactively

            TypeError: fit_transform() missing 1 required positional argument: 'X'