Attached example shows a table of dates and week #'s.
For any given date in column Y, the matching week # in column Z.
Thanking you in advance.
Attached example shows a table of dates and week #'s.
For any given date in column Y, the matching week # in column Z.
Thanking you in advance.
So what is wrong with the results you are getting now? Where are your expected results? Not really sure what you are wanting from us.
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.
This formula looks up the value in the appropriate year's column.
Please try in Z3 and copy down:Formula:Please Login or Register to view this content.
Last edited by HansDouwe; 07-04-2023 at 05:19 AM.
@Hans: I am somewhat bemused. When I was working on this, YEAR(Y5) returned 1930, yet, for you, it returns 2030. How/why is that? Testing on an iPad, if that makes a difference.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Apologies the formula in Z3 should have been deleted. The lookup encompasses the entire table to give the week # values in col Z for any date in col Y.
@TMS, I was cheating, because I changed the value of cell Y5 from 31-12-1930 to 31-12-2030.
@TMS
in OP the date is 31.12.1930. I think Hans updated the date in this century
Diana Tanase
If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
Hans, your formula does the trick.
Cheers Ian
Thanks for the feedback and rep , gald to have helped.
@Diana: thank you. (Surprisingly,) I hadn't looked at the actual date. I just thought it must be something to do with a date algorithm I suspect the OP put in a two digit year (30) and THAT generated the 1930 year. I had a working formula until that row. Then I saw Hans had provided a solution so I threw in the towel.
Cell Z3 formula , Drag down
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks