Find first Sunday in April using Excel

  Chris the Ancient 10:33 18 Oct 10
Locked

Using Excel 2003.

Without going into the 'whys and wherefores' of the need, I need a formula/function/macro (or whatever) that given a year in "yyyy" format, I can get a result in "dd/mm/yyyy" showing the date of the first Sunday in April.

Any ideas/thoughts/suggestions, please?

  VoG II 10:46 18 Oct 10

With the year in A1 try

=DATE(A1,4,CHOOSE(WEEKDAY(DATE(A1,4,1)),2,1,7,6,5,4,3))-1

  Chris the Ancient 10:54 18 Oct 10

That was flippin' quick! I was still researching (unsuccessfully) in Google for ideas.

That works absolutely brilliantly!

Next challenge (for me) is to work out how it works.

I think my brain might just explode!

CtA

  VoG II 11:04 18 Oct 10

I cheated!

I used the first Monday formula from here click here and subtracted 1. I think this will give an error if the first Monday is the 1st. This is better

=DATE(A1,4,CHOOSE(WEEKDAY(DATE(A1,4,1)),1,7,6,5,4,3,2))

  Chris the Ancient 11:15 18 Oct 10

I like cheating... especially when it works!

That one didn't show up in my search - my string must have been a bit to specific.

I'm now going to 'play' with both formulas and see - especially as 2013 has 1st April on a Monday.

  Chris the Ancient 11:18 18 Oct 10

Yup.

Second on works better in 2013!

  Chris the Ancient 11:19 18 Oct 10

Sorry. Forgot that magic word (the ones that my kids never used - back when I had them!)

Thanks!

This thread is now locked and can not be replied to.

Surface Pro 5 News - release date, UK price, features, specs

Microsoft Surface Studio hands-on review: the iMac killer is here

Best Mac antivirus 2017