Years when 1 April occurs on a Saturday

[R B]

Can someone help me with a calculation to identify the years between 1970
and 1990 when 1 April was a Saturday please?

 

[K.S.Warrier]

Hi,
1 April 1970 is Wednesday.After each year,April 1 will be the next week
day.ie,1 April 1971 will be Thursday.But when the year is a leap year, it
will be the next week day.Hence 1 April 1972 will be Saturday(as in between,
February has 29 days) in a leap year.Based on this, there will be only 3
saturdays on 1 April 1972,1978 & 1989.A formula can be arrived ,but not so
easy.I have created a prpectual calendar & can get name of week on any date
after 1900(which year is not a leap year,even though 1900 is divisible by 4.
Thank you
k.s.Warrier

 

[Bernd Plumhoff]

Write in cells A1:A21 the year numbers 1970 till 1990.
In B1 write:
=WEEKDAY(DATE(A1+ROW()-1,4,1),2)=6

and copy down.

HTH,
Bernd

 

[Biff]

Hi!

=WEEKDAY(DATE(A1+ROW()-1,4,1),2)=6

This leads to incorrect results. ROW()-1 causes the
problem.

=WEEKDAY(DATE(A1,4,1),2)=6

Works just fine.

Biff

 

[Ken Wright]

The answer Biff/Bernd gave you will do what you ask, but for no other reason
than that I was curious to try and do it in a single formula with no helper
cells:-

Taking their formula and introducing an array element - Given that you have 21
years in your spread, select cells A1:A21, paste in the following and array
enter it:-

=IF(LARGE((WEEKDAY(DATE(ROW(INDIRECT("1970:1990")),4,1),2)=6)*(DATE(ROW(INDIRECT("1970:1990")),4,1)),ROW(INDIRECT("1:21"))),LARGE((WEEKDAY(DATE(ROW(INDIRECT("1970:1990")),4,1),2)=6)*(DATE(ROW(INDIRECT("1970:1990")),4,1)),ROW(INDIRECT("1:21"))),"")

Format cells as custom yyyy

Results

1989
1978
1972