LinkBack URL
About LinkBacks
The 15th December 2019 was a Sunday. How do I find the 'nearest' corresponding Sunday in 2018 which, in this example, was 16th December?
The 15th December 2019 was a Sunday. How do I find the 'nearest' corresponding Sunday in 2018 which, in this example, was 16th December?
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)
Last edited by Ace_XL; 01-01-2020 at 06:31 PM.
Life's a spreadsheet, Excel!
Say thanks, Click *
Ace indeed! Thanks, I never knew edate existed.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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
will think about tweaking, perhaps
=EDATE(A1,-12)+1+AND(MOD(YEAR(A1),4)=0,MONTH(A1)>2)+AND(MOD(YEAR(A1),4)=1,MONTH(A1)<3)
Last edited by davsth; 01-02-2020 at 04:37 AM.
Hi,
=A1-364
Greetings
Christian
Christian - welcome to the forum.
Your solution won't work for a leap year.
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
I am inclined to agree and think I reached this conclusion when asked a similar question last year. I agree with the logic
Yes, I suppose you will - guess I was over-thinking it!
@ 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.
Last edited by Ace_XL; 01-02-2020 at 11:16 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks