Convert an Excel date code to a “date”
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
|
show 2 more comments
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
1
Does every year have a 0th of January and 29th of February or is 1900 the only anomaly?
– Shaggy
Nov 27 '18 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 '18 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 '18 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 theDayOfWeek
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 '18 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 '18 at 16:31
|
show 2 more comments
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
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
code-golf date conversion
edited Nov 28 '18 at 12:58
Glorfindel
137119
137119
asked Nov 27 '18 at 22:13
Adám
28.8k269190
28.8k269190
1
Does every year have a 0th of January and 29th of February or is 1900 the only anomaly?
– Shaggy
Nov 27 '18 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 '18 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 '18 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 theDayOfWeek
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 '18 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 '18 at 16:31
|
show 2 more comments
1
Does every year have a 0th of January and 29th of February or is 1900 the only anomaly?
– Shaggy
Nov 27 '18 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 '18 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 '18 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 theDayOfWeek
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 '18 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 '18 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 '18 at 22:24
Does every year have a 0th of January and 29th of February or is 1900 the only anomaly?
– Shaggy
Nov 27 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 16:31
|
show 2 more comments
10 Answers
10
active
oldest
votes
Excel, 3(+7?)
=A1
with format
yyy/m/d
Pure port
The output format may of course vary according to your locale.
– Adám
Nov 27 '18 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 '18 at 15:37
@Keeta For this to work on Excel for Mac, simply uncheck "Use 1904 date system" in preferences.
– BradC
Nov 28 '18 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 '18 at 20:28
add a comment |
k (kdb+ 3.5), 55 54 51 50 bytes
{$(`1900.01.00`1900.02.29,"d"$x-36526-x<60)0 60?x}
to test, paste this line in the q console:
k)-1@{$(`1900.01.00`1900.02.29,"d"$x-36526-x<60)0 60?x}'0 1 2 59 60 61 100 1000 10000 100000;
the output should be
1900.01.00
1900.01.01
1900.01.02
1900.02.28
1900.02.29
1900.03.01
1900.04.09
1902.09.26
1927.05.18
2173.10.14
{
}
is a function with argument x
0 60?x
index of x
among 0 60
or 2 if not found
ˋ1900.01.00ˋ1900.02.29
a list of two symbols
,
append to it
"d"$
converted to a date
x-36526
number of days since 1900 (instead of the default 2000)
- x<60
adjust for excel's leap error
(ˋ1900.01.00ˋ1900.02.29,"d"$x-36526-x<60)
@0 60?x
juxtaposition means indexing - the "@" in the middle is implicit
$
convert to string
1
For a different version of k (k5/k6, I think),{$[x;$`d$x-65746;"1900.01.00"]}
seems to work. I assume something overflows somewhere for100000
.
– zgrep
Nov 28 '18 at 12:53
@zgrep You should post since versions of K basically are entirely dissimilar languages.
– Adám
Nov 28 '18 at 16:59
add a comment |
Python 2, 111 bytes
from datetime import*
n=input()
print('1900-0'+'12--0209'[n>9::2],date(1900,1,1)+timedelta(n+~(n>59)))[0<n!=60]
Try it online!
-5 thanks to ngn.
Note: I'm pretty sure this will turn out to be longer as a lambda, since the format of the result shouldn't vary.
– Erik the Outgolfer
Nov 27 '18 at 22:55
add a comment |
JavaScript (ES6), 89 82 77 bytes
Saved 7 12 bytes thanks to @tsh
n=>(p=n>60?'':19)+new Date(p*400,0,n-!p||1).toJSON().slice(p/9,10-!n)+(n&&'')
Try it online!
n=>n?n-60?new Date(1900,0,n-(n>60)).toJSON().slice(0,10):'1900-02-29':'1900-01-00'
– tsh
Nov 28 '18 at 10:52
@tsh That's much better indeed. Thanks. (Also, I wonder if this approach could somehow be golfed.)
– Arnauld
Nov 28 '18 at 11:12
I just find outnew Date(0,0,1)
is same asnew Date(1900,0,1)
. So remove190
saves 3 bytes. And...
– tsh
Nov 29 '18 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 '18 at 6:25
Does it need to be run in GMT0/-x?
– l4m2
Nov 29 '18 at 7:37
|
show 2 more comments
Clean, 205 189 bytes
import StdEnv
a=30;b=31;c=1900;r=rem
@m=sum(take m(?c))
?n=[b,if(n>c&&(r n 4>0||r n 100<1&&r n 400>0))28 29,b,a,b,a,b,b,a,b,a,b: ?(n+1)]
$n#m=while(m= @m<n)inc 0-1
=(c+m/12,1+r m 12,n- @m)
Try it online!
1
First answer that doesn't use built-in date handling. Nice!
– Adám
Nov 29 '18 at 6:07
add a comment |
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
add a comment |
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!
add a comment |
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
add a comment |
Perl 6, 81 bytes
{$_??$_-60??Date.new-from-daycount($_+15018+(60>$_))!!'1900-02-29'!!'1900-01-00'}
Try it online!
add a comment |
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
.
add a comment |
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',
autoActivateHeartbeat: false,
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Excel, 3(+7?)
=A1
with format
yyy/m/d
Pure port
The output format may of course vary according to your locale.
– Adám
Nov 27 '18 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 '18 at 15:37
@Keeta For this to work on Excel for Mac, simply uncheck "Use 1904 date system" in preferences.
– BradC
Nov 28 '18 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 '18 at 20:28
add a comment |
Excel, 3(+7?)
=A1
with format
yyy/m/d
Pure port
The output format may of course vary according to your locale.
– Adám
Nov 27 '18 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 '18 at 15:37
@Keeta For this to work on Excel for Mac, simply uncheck "Use 1904 date system" in preferences.
– BradC
Nov 28 '18 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 '18 at 20:28
add a comment |
Excel, 3(+7?)
=A1
with format
yyy/m/d
Pure port
Excel, 3(+7?)
=A1
with format
yyy/m/d
Pure port
answered Nov 27 '18 at 23:02
community wiki
l4m2
The output format may of course vary according to your locale.
– Adám
Nov 27 '18 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 '18 at 15:37
@Keeta For this to work on Excel for Mac, simply uncheck "Use 1904 date system" in preferences.
– BradC
Nov 28 '18 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 '18 at 20:28
add a comment |
The output format may of course vary according to your locale.
– Adám
Nov 27 '18 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 '18 at 15:37
@Keeta For this to work on Excel for Mac, simply uncheck "Use 1904 date system" in preferences.
– BradC
Nov 28 '18 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 '18 at 20:28
The output format may of course vary according to your locale.
– Adám
Nov 27 '18 at 23:03
The output format may of course vary according to your locale.
– Adám
Nov 27 '18 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 '18 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 '18 at 15:37
@Keeta For this to work on Excel for Mac, simply uncheck "Use 1904 date system" in preferences.
– BradC
Nov 28 '18 at 19:45
@Keeta For this to work on Excel for Mac, simply uncheck "Use 1904 date system" in preferences.
– BradC
Nov 28 '18 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 '18 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 '18 at 20:28
add a comment |
k (kdb+ 3.5), 55 54 51 50 bytes
{$(`1900.01.00`1900.02.29,"d"$x-36526-x<60)0 60?x}
to test, paste this line in the q console:
k)-1@{$(`1900.01.00`1900.02.29,"d"$x-36526-x<60)0 60?x}'0 1 2 59 60 61 100 1000 10000 100000;
the output should be
1900.01.00
1900.01.01
1900.01.02
1900.02.28
1900.02.29
1900.03.01
1900.04.09
1902.09.26
1927.05.18
2173.10.14
{
}
is a function with argument x
0 60?x
index of x
among 0 60
or 2 if not found
ˋ1900.01.00ˋ1900.02.29
a list of two symbols
,
append to it
"d"$
converted to a date
x-36526
number of days since 1900 (instead of the default 2000)
- x<60
adjust for excel's leap error
(ˋ1900.01.00ˋ1900.02.29,"d"$x-36526-x<60)
@0 60?x
juxtaposition means indexing - the "@" in the middle is implicit
$
convert to string
1
For a different version of k (k5/k6, I think),{$[x;$`d$x-65746;"1900.01.00"]}
seems to work. I assume something overflows somewhere for100000
.
– zgrep
Nov 28 '18 at 12:53
@zgrep You should post since versions of K basically are entirely dissimilar languages.
– Adám
Nov 28 '18 at 16:59
add a comment |
k (kdb+ 3.5), 55 54 51 50 bytes
{$(`1900.01.00`1900.02.29,"d"$x-36526-x<60)0 60?x}
to test, paste this line in the q console:
k)-1@{$(`1900.01.00`1900.02.29,"d"$x-36526-x<60)0 60?x}'0 1 2 59 60 61 100 1000 10000 100000;
the output should be
1900.01.00
1900.01.01
1900.01.02
1900.02.28
1900.02.29
1900.03.01
1900.04.09
1902.09.26
1927.05.18
2173.10.14
{
}
is a function with argument x
0 60?x
index of x
among 0 60
or 2 if not found
ˋ1900.01.00ˋ1900.02.29
a list of two symbols
,
append to it
"d"$
converted to a date
x-36526
number of days since 1900 (instead of the default 2000)
- x<60
adjust for excel's leap error
(ˋ1900.01.00ˋ1900.02.29,"d"$x-36526-x<60)
@0 60?x
juxtaposition means indexing - the "@" in the middle is implicit
$
convert to string
1
For a different version of k (k5/k6, I think),{$[x;$`d$x-65746;"1900.01.00"]}
seems to work. I assume something overflows somewhere for100000
.
– zgrep
Nov 28 '18 at 12:53
@zgrep You should post since versions of K basically are entirely dissimilar languages.
– Adám
Nov 28 '18 at 16:59
add a comment |
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
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
edited Nov 28 '18 at 9:58
answered Nov 27 '18 at 23:04
ngn
6,94112559
6,94112559
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 for100000
.
– zgrep
Nov 28 '18 at 12:53
@zgrep You should post since versions of K basically are entirely dissimilar languages.
– Adám
Nov 28 '18 at 16:59
add a comment |
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 for100000
.
– zgrep
Nov 28 '18 at 12:53
@zgrep You should post since versions of K basically are entirely dissimilar languages.
– Adám
Nov 28 '18 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 '18 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 '18 at 12:53
@zgrep You should post since versions of K basically are entirely dissimilar languages.
– Adám
Nov 28 '18 at 16:59
@zgrep You should post since versions of K basically are entirely dissimilar languages.
– Adám
Nov 28 '18 at 16:59
add a comment |
Python 2, 111 bytes
from datetime import*
n=input()
print('1900-0'+'12--0209'[n>9::2],date(1900,1,1)+timedelta(n+~(n>59)))[0<n!=60]
Try it online!
-5 thanks to ngn.
Note: I'm pretty sure this will turn out to be longer as a lambda, since the format of the result shouldn't vary.
– Erik the Outgolfer
Nov 27 '18 at 22:55
add a comment |
Python 2, 111 bytes
from datetime import*
n=input()
print('1900-0'+'12--0209'[n>9::2],date(1900,1,1)+timedelta(n+~(n>59)))[0<n!=60]
Try it online!
-5 thanks to ngn.
Note: I'm pretty sure this will turn out to be longer as a lambda, since the format of the result shouldn't vary.
– Erik the Outgolfer
Nov 27 '18 at 22:55
add a comment |
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.
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.
edited Nov 28 '18 at 13:16
answered Nov 27 '18 at 22:52
Erik the Outgolfer
31.4k429103
31.4k429103
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 '18 at 22:55
add a comment |
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 '18 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 '18 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 '18 at 22:55
add a comment |
JavaScript (ES6), 89 82 77 bytes
Saved 7 12 bytes thanks to @tsh
n=>(p=n>60?'':19)+new Date(p*400,0,n-!p||1).toJSON().slice(p/9,10-!n)+(n&&'')
Try it online!
n=>n?n-60?new Date(1900,0,n-(n>60)).toJSON().slice(0,10):'1900-02-29':'1900-01-00'
– tsh
Nov 28 '18 at 10:52
@tsh That's much better indeed. Thanks. (Also, I wonder if this approach could somehow be golfed.)
– Arnauld
Nov 28 '18 at 11:12
I just find outnew Date(0,0,1)
is same asnew Date(1900,0,1)
. So remove190
saves 3 bytes. And...
– tsh
Nov 29 '18 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 '18 at 6:25
Does it need to be run in GMT0/-x?
– l4m2
Nov 29 '18 at 7:37
|
show 2 more comments
JavaScript (ES6), 89 82 77 bytes
Saved 7 12 bytes thanks to @tsh
n=>(p=n>60?'':19)+new Date(p*400,0,n-!p||1).toJSON().slice(p/9,10-!n)+(n&&'')
Try it online!
n=>n?n-60?new Date(1900,0,n-(n>60)).toJSON().slice(0,10):'1900-02-29':'1900-01-00'
– tsh
Nov 28 '18 at 10:52
@tsh That's much better indeed. Thanks. (Also, I wonder if this approach could somehow be golfed.)
– Arnauld
Nov 28 '18 at 11:12
I just find outnew Date(0,0,1)
is same asnew Date(1900,0,1)
. So remove190
saves 3 bytes. And...
– tsh
Nov 29 '18 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 '18 at 6:25
Does it need to be run in GMT0/-x?
– l4m2
Nov 29 '18 at 7:37
|
show 2 more comments
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!
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!
edited Nov 29 '18 at 8:32
answered Nov 28 '18 at 7:39
Arnauld
72.4k689305
72.4k689305
n=>n?n-60?new Date(1900,0,n-(n>60)).toJSON().slice(0,10):'1900-02-29':'1900-01-00'
– tsh
Nov 28 '18 at 10:52
@tsh That's much better indeed. Thanks. (Also, I wonder if this approach could somehow be golfed.)
– Arnauld
Nov 28 '18 at 11:12
I just find outnew Date(0,0,1)
is same asnew Date(1900,0,1)
. So remove190
saves 3 bytes. And...
– tsh
Nov 29 '18 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 '18 at 6:25
Does it need to be run in GMT0/-x?
– l4m2
Nov 29 '18 at 7:37
|
show 2 more comments
n=>n?n-60?new Date(1900,0,n-(n>60)).toJSON().slice(0,10):'1900-02-29':'1900-01-00'
– tsh
Nov 28 '18 at 10:52
@tsh That's much better indeed. Thanks. (Also, I wonder if this approach could somehow be golfed.)
– Arnauld
Nov 28 '18 at 11:12
I just find outnew Date(0,0,1)
is same asnew Date(1900,0,1)
. So remove190
saves 3 bytes. And...
– tsh
Nov 29 '18 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 '18 at 6:25
Does it need to be run in GMT0/-x?
– l4m2
Nov 29 '18 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 '18 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 '18 at 10:52
@tsh That's much better indeed. Thanks. (Also, I wonder if this approach could somehow be golfed.)
– Arnauld
Nov 28 '18 at 11:12
@tsh That's much better indeed. Thanks. (Also, I wonder if this approach could somehow be golfed.)
– Arnauld
Nov 28 '18 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 '18 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 '18 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 '18 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 '18 at 6:25
Does it need to be run in GMT0/-x?
– l4m2
Nov 29 '18 at 7:37
Does it need to be run in GMT0/-x?
– l4m2
Nov 29 '18 at 7:37
|
show 2 more comments
Clean, 205 189 bytes
import StdEnv
a=30;b=31;c=1900;r=rem
@m=sum(take m(?c))
?n=[b,if(n>c&&(r n 4>0||r n 100<1&&r n 400>0))28 29,b,a,b,a,b,b,a,b,a,b: ?(n+1)]
$n#m=while(m= @m<n)inc 0-1
=(c+m/12,1+r m 12,n- @m)
Try it online!
1
First answer that doesn't use built-in date handling. Nice!
– Adám
Nov 29 '18 at 6:07
add a comment |
Clean, 205 189 bytes
import StdEnv
a=30;b=31;c=1900;r=rem
@m=sum(take m(?c))
?n=[b,if(n>c&&(r n 4>0||r n 100<1&&r n 400>0))28 29,b,a,b,a,b,b,a,b,a,b: ?(n+1)]
$n#m=while(m= @m<n)inc 0-1
=(c+m/12,1+r m 12,n- @m)
Try it online!
1
First answer that doesn't use built-in date handling. Nice!
– Adám
Nov 29 '18 at 6:07
add a comment |
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!
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!
edited Nov 29 '18 at 6:31
answered Nov 29 '18 at 5:55
Οurous
6,44311033
6,44311033
1
First answer that doesn't use built-in date handling. Nice!
– Adám
Nov 29 '18 at 6:07
add a comment |
1
First answer that doesn't use built-in date handling. Nice!
– Adám
Nov 29 '18 at 6:07
1
1
First answer that doesn't use built-in date handling. Nice!
– Adám
Nov 29 '18 at 6:07
First answer that doesn't use built-in date handling. Nice!
– Adám
Nov 29 '18 at 6:07
add a comment |
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
add a comment |
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
add a comment |
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
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
edited Nov 28 '18 at 17:14
answered Nov 28 '18 at 14:40
Shaggy
18.9k21666
18.9k21666
add a comment |
add a comment |
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!
add a comment |
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!
add a comment |
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!
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!
answered Nov 29 '18 at 8:03
cobaltp
35618
35618
add a comment |
add a comment |
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
add a comment |
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
add a comment |
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
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
edited Nov 29 '18 at 16:26
Adám
28.8k269190
28.8k269190
answered Nov 29 '18 at 15:45
ngn
6,94112559
6,94112559
add a comment |
add a comment |
Perl 6, 81 bytes
{$_??$_-60??Date.new-from-daycount($_+15018+(60>$_))!!'1900-02-29'!!'1900-01-00'}
Try it online!
add a comment |
Perl 6, 81 bytes
{$_??$_-60??Date.new-from-daycount($_+15018+(60>$_))!!'1900-02-29'!!'1900-01-00'}
Try it online!
add a comment |
Perl 6, 81 bytes
{$_??$_-60??Date.new-from-daycount($_+15018+(60>$_))!!'1900-02-29'!!'1900-01-00'}
Try it online!
Perl 6, 81 bytes
{$_??$_-60??Date.new-from-daycount($_+15018+(60>$_))!!'1900-02-29'!!'1900-01-00'}
Try it online!
answered Nov 28 '18 at 11:06
nwellnhof
6,49511125
6,49511125
add a comment |
add a comment |
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
.
add a comment |
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
.
add a comment |
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
.
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
.
edited Nov 28 '18 at 17:46
answered Nov 28 '18 at 15:45
BradC
3,719523
3,719523
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
1
Does every year have a 0th of January and 29th of February or is 1900 the only anomaly?
– Shaggy
Nov 27 '18 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 '18 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 '18 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 '18 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 '18 at 16:31