Revised Julian Calendar Leap Year Rules 1. Year perfectly divisible by 4. 2. Century years when divided by 900 leave the remainder of either 200 or 600. In Gregorian calendar, rule 1 is same but for century years, it has to be perfectly divisible by 400. Find the Years between 1901 to 9999 where a year is leap year either in Gregorian or Revised Julian calendar but not in other. Hence, it should be a leap year in one system but not in both. Hence, both systems disagree for these years from leap year perspective. Ex. 2800 – This is a leap year in Gregorian calendar as perfectly divisible by 400 but when divided by 900, it leaves a remainder of 100, hence not a leap year in Revised Julian calendar. 2900 – When divided by 900, it leaves a remainder of 200, hence a leap year in Revised Julian Calendar. But it is not perfectly divisible by 400, hence not a leap year in Gregorian Calendar.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 422
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Disagreeing Leap Years with Power Query
Power Query solution 1 for Find Disagreeing Leap Years, proposed by Aditya Kumar Darak 🇮🇳:
let
Generate = List.Generate(
() => [a = 19, d = 0],
each [a] < 100,
each [
a = [a] + 1,
b = Number.Mod(a, 9),
c = Number.Mod(a, 4),
d = Number.BitwiseXor(Number.From(b = 2 or b = 6), Number.From(c = 0))
],
each [Answer = [a] * 100, TF = [d]]
),
Table = Table.FromRecords(Generate),
Return = Table.SelectRows(Table, each [TF] = 1)[Answer]
in
ReturnSolving the challenge of Find Disagreeing Leap Years with Excel
Excel solution 1 for Find Disagreeing Leap Years, proposed by Bo Rydobon 🇹🇭:
=LET(y,
SEQUENCE(
81,
,
19
),
j,
MOD(
y,
9
),
FILTER(y,
(j=2)+(j=6)+(MOD(
y,
4
)=0)=1)*100)Excel solution 2 for Find Disagreeing Leap Years, proposed by Rick Rothstein:
=LET(s,
SEQUENCE(
8099,
,
1901
),
j,
FILTER(
s,
MAP(
s,
LAMBDA(
y,
OR(
MOD(
y,
900
)={200,
600}
)
)
)
),
g,
FILTER(s,
(MOD(
s,
100
)=0)*(DAY(
DATE(
s,
3,
)
)=29)),
SORT(
UNIQUE(
VSTACK(
j,
g
),
,
1
)
))Excel solution 3 for Find Disagreeing Leap Years, proposed by John V.:
=LET(a,
ROW(
19:99
),
FILTER(a,
(MOD(
a,
4
)=0)+BYROW(
MOD(
a,
9
)={2,
6},
OR
)=1)/1%)Excel solution 4 for Find Disagreeing Leap Years, proposed by محمد حلمي:
=LET(y,
SEQUENCE(
8099
)+1900,
e,
MOD(
y,
900
),
j,
MOD(
y,
4
)=0,
x,
FILTER(y,
j*(MOD(
y,
400
)=0)),
n,
FILTER(y,
j*((e=200)+(e=600))),
SORT(
VSTACK(
FILTER(
x,
ISNA(
XMATCH(
x,
n
)
)
),
FILTER(
n,
ISNA(
XMATCH(
n,
x
)
)
)
)
))Excel solution 5 for Find Disagreeing Leap Years, proposed by Kris Jaganah:
=LET(a,
SEQUENCE((99-20+1),
,
2000,
100),
TOCOL(a/(MMULT(HSTACK(MMULT(--(MOD(
a,
900
)={200,
600}),
{1;1}),
--(INT(
a/400
)<>(
a/400
))),
{1;1})<>1),
3))Excel solution 6 for Find Disagreeing Leap Years, proposed by Julian Poeltl:
=LET(
S,
SEQUENCE(
8098,
,
1901
),
X,
BYROW(
S,
LAMBDA(
A,
XOR(
MOD(
A,
900
)=200,
MOD(
A,
900
)=600,
MOD(
A,
400
)=0
)
)
),
FILTER(
S,
X
)
)Excel solution 7 for Find Disagreeing Leap Years, proposed by Timothée BLIOT:
=LET(Y,
SEQUENCE(
8099,
,
1901
),
FILTER(Y,
(--((--(MOD(
Y,
900
)=200)+--(MOD(
Y,
900
)=600))>0)+--(MOD(
Y,
400
)=0))=1))Excel solution 8 for Find Disagreeing Leap Years, proposed by Sunny Baggu:
=LET(
_y,
SEQUENCE(
9999 - 1901 + 1,
,
1901
),
_lp,
FILTER(
_y,
MOD(
_y,
4
) = 0
),
_j,
FILTER(
_lp,
BYROW(
MOD(
_lp,
900
) = {200,
600},
LAMBDA(
a,
OR(
a
)
)
)
),
_g,
FILTER(
_lp,
MOD(
_lp,
400
) = 0
),
SORT(
UNIQUE(
VSTACK(
_j,
_g
),
,
1
)
)
)Excel solution 9 for Find Disagreeing Leap Years, proposed by Charles Roldan:
=LET(
y,
SEQUENCE(
80,
1,
2000,
100
),
J,
ISNUMBER(
XMATCH(
MOD(
y,
900
),
{200,
600}
)
),
G,
MOD(
y,
400
)=0,
FILTER(
y,
J<>G
)
)Excel solution 10 for Find Disagreeing Leap Years, proposed by Charles Roldan:
=LET(
y,
SEQUENCE(
80,
,
20
),
f,
LAMBDA(
d,
a,
ISNUMBER(
XMATCH(
MOD(
y,
d
),
a
)
)
),
100*FILTER(
y,
f(
9,
{2,
6}
)<>f(
4,
0
)
)
)Excel solution 11 for Find Disagreeing Leap Years, proposed by Andy Heybruch:
=LET(_yr,
SEQUENCE(
9999-1901+1,
,
1901
),
_centyr,
FILTER(
_yr,
MOD(
_yr,
100
)=0
),
_jul,
((MOD(
_centyr,
900
)=200)+(MOD(
_centyr,
900
)=600))*_centyr,
_greg,
(MOD(
_centyr,
400
)=0)*_centyr,
_out,
((_greg<>_jul)*(_greg+_jul)),
FILTER(
_out,
_out>0
))Excel solution 12 for Find Disagreeing Leap Years, proposed by Alexandra Popoff:
= LAMBDA(z_Min,
z_max,
LET(
z_seq,
SEQUENCE(
z_max - z_Min + 1,
1,
z_Min,
1
),
z_test_4,
MOD(
z_seq,
4
) = 0,
z_test_Cent,
(MOD(
z_seq,
900
) = 200) +
(MOD(
z_seq,
900
) = 600),
z_test_greg,
MOD(
z_seq,
400
) = 0,
FILTER(z_seq,
z_test_4 *
(((z_test_Cent <> 0) * (z_test_greg = FALSE)) +
((z_test_Cent = 0) * (z_test_greg = TRUE)))
<> 0
)));
Fx_Cal_ByRow = LAMBDA(z_Min,
z_max,
LET(
z_seq,
SEQUENCE(
z_max - z_Min + 1,
1,
z_Min,
1
),
FILTER(z_seq,
BYROW(z_seq,
LAMBDA(z_i,
LET(
z_test_4,
MOD(
z_i,
4
) = 0,
z_test_Cent,
((MOD(
z_i,
900
) = 200) + (MOD(
z_i,
900
) = 600)) <> 0,
z_test_greg,
MOD(
z_i,
400
) = 0,
AND(
z_test_4,
OR(
AND(
z_test_Cent = TRUE,
z_test_greg = FALSE
),
AND(
z_test_Cent = FALSE,
z_test_greg = TRUE
)
)
)
))))))Solving the challenge of Find Disagreeing Leap Years with Python
Python solution 1 for Find Disagreeing Leap Years, proposed by Konrad Gryczan, PhD:
import pandas as pd
test = pd.read_excel('422 Leap Years in Julian and Gregorian.xlsx', sheet_name='Sheet1', usecols="A", nrows=27)
years = pd.DataFrame({'range': range(1901, 10000)})
def is_greg_leap(year):
if year % 4 == 0 and not year % 100 == 0:
return True
elif year % 100 == 0 and year % 400 == 0:
return True
else:
return False
def is_revjul_leap(year):
if year % 4 == 0 and not year % 100 == 0:
return True
elif year % 100 == 0 and year % 900 in [200, 600]:
return True
else:
return False
leap_years = years.assign(greg_leap=years['range'].apply(is_greg_leap),
revjul_leap=years['range'].apply(is_revjul_leap))
leap_years = leap_years[leap_years['greg_leap'] != leap_years['revjul_leap']]
leap_years = leap_years[['range']].reset_index(drop=True)
print(leap_years["range"].equals(test["Expected Answer"])) # True
Solving the challenge of Find Disagreeing Leap Years with Python in Excel
Python in Excel solution 1 for Find Disagreeing Leap Years, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_422 - Leap Years in Julian and Gregorian.xlsx'
df = pd.read_excel(file_path)
def revised_julian_leap_year(number):
if not number % 4:
if not number % 100: return number % 900 in (200, 600)
else: return True
def gregorian_leap_year(number):
if not number % 4:
if not number % 100: return not number % 400
else: return True
def leap_julian_xor_gregorian(numbers):
values = []
for number in numbers:
a = revised_julian_leap_year(number)
b = gregorian_leap_year(number)
if a != b:
values.append(number)
return values
df['My Answer'] = pd.Series(leap_julian_xor_gregorian(range(1901, 10000)))
print(df)
Solving the challenge of Find Disagreeing Leap Years with R
R solution 1 for Find Disagreeing Leap Years, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
test = read_excel("Excel/422 Leap Years in Julian and Gregorian.xlsx", range = "A1:A27")
years = tibble(range = 1901:9999)
is_greg_leap = function(year){
if(year %% 4 == 0 && !year %% 100 == 0){
return(TRUE)
} else if(year %% 100 == 0 && year %% 400 == 0){
return(TRUE)
} else {
return(FALSE)
}
}
is_revjul_leap = function(year){
if(year %% 4 == 0 && !year %% 100 == 0){
return(TRUE)
} else if(year %% 100 == 0 && year %% 900 %in% c(200, 600)){
return(TRUE)
} else {
return(FALSE)
}
}
leap_years = years %>%
mutate(greg_leap = map_lgl(range, is_greg_leap),
revjul_leap = map_lgl(range, is_revjul_leap)) %>%
filter(greg_leap != revjul_leap) %>%
select(range)
&&&