Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
R B
 
Posts: n/a
Default 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?



  #2   Report Post  
K.S.Warrier
 
Posts: n/a
Default

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?




  #3   Report Post  
Bernd Plumhoff
 
Posts: n/a
Default

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


  #4   Report Post  
Biff
 
Posts: n/a
Default

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


.

  #5   Report Post  
Bernd Plumhoff
 
Posts: n/a
Default

Biff,

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

Regards,
Bernd




  #6   Report Post  
Ken Wright
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Years of Service formula.... Wild Bill Excel Discussion (Misc queries) 1 December 6th 04 09:47 PM
How do I display months and years between two dates JSmith Excel Discussion (Misc queries) 1 November 30th 04 05:41 PM


All times are GMT +1. The time now is 05:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"