Years when 1 April occurs on a Saturday
 

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

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

"R B" wrote:

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

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

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

-----Original Message-----
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,

I thank you! I started without Column A (A1 was just constant 1970) and
forgot to adjust the formula.

Regards,
Bernd

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(IN DIRECT("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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Biff" wrote in message
...
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

-----Original Message-----
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


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004