# Nearest corresponding day

1. ## Nearest corresponding day

The 15th December 2019 was a Sunday. How do I find the 'nearest' corresponding Sunday in 2018 which, in this example, was 16th December?

2. ## Re: Nearest corresponding day

Try with your date in A1

=EDATE(A1,-12)+1

If you want this to work for any year use
=EDATE(A1,-12)+1+(MOD(YEAR(A1),4)=0)

3. ## Re: Nearest corresponding day

Ace indeed! Thanks, I never knew edate existed.

5. ## Re: Nearest corresponding day

I am not quite sure the formula will work for the first 2 months of a leap year as the MOD(YEAR(A1),4) does not apply

=EDATE(A1,-12)+1+AND(MOD(YEAR(A1),4)=0,MONTH(A1)>2)+AND(MOD(YEAR(A1),4)=1,MONTH(A1)<3)

Hi,

=A1-364

Greetings

Christian

7. ## Re: Nearest corresponding day

Christian - welcome to the forum.

Your solution won't work for a leap year.

8. ## Re: Nearest corresponding day

Why not?
If I subtract 364 (52*7) days from a date, i will always get the same Weekday, if there is a leap year or not.

Greetings

Christian

9. ## Re: Nearest corresponding day

I am inclined to agree and think I reached this conclusion when asked a similar question last year. I agree with the logic

10. ## Re: Nearest corresponding day

Yes, I suppose you will - guess I was over-thinking it!

11. ## Re: Nearest corresponding day

@ BrisbaneBob
A note of caution

The only time Besserwiser's won't work is for the date Dec 31 (any year) and Dec 30 (in leap years with Besserwiser's solution only) as they would give a date in the same year

Can you live with that? Else you can always build in an IF statement to handle that.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1