Ramballah
Active Member
 Joined
 Sep 25, 2018
 Messages
 255
 Office Version

 2019
 Platform

 Windows
Hello everyone,
I have a sheet where we take dates from column A and move them into a year and week formula for our sumifs + vlookups. However because of week 53 it gets really messed up.
So I hope by having proper dates it might atleast be somewhat fixed if not just more correct
See my sheet below:
In Cell A1 I use the formula: =MIN(K:K)
In Cell A2 I use the formula: =A1
In Cell A3 I use the formula: =A1+7
In column B I use the formula's : =CONCATENATE(YEAR(A2);"/";WEEKNUM(A2;21))
This way in column B only the weeks will show up that are still open, so I won't see week 15 for example.
However it is doing that by taking the lowest date in column K. That date can either be a monday, a sunday or any other day.
Our weeks (at least in this file) go from wednesday till wednesday since we pay our invoices on Wednesday.
Is there some magic formula for column A that will go from 01012020 to all the incoming weeks, without showing me weeks that we have already passed. So I only want to see future weeks!
I am sorry if I am a bit vague.
Thanks in advance
Ram
the excel file: https://easyupload.io/0py3zd
I have a sheet where we take dates from column A and move them into a year and week formula for our sumifs + vlookups. However because of week 53 it gets really messed up.
So I hope by having proper dates it might atleast be somewhat fixed if not just more correct
See my sheet below:
In Cell A1 I use the formula: =MIN(K:K)
In Cell A2 I use the formula: =A1
In Cell A3 I use the formula: =A1+7
In column B I use the formula's : =CONCATENATE(YEAR(A2);"/";WEEKNUM(A2;21))
This way in column B only the weeks will show up that are still open, so I won't see week 15 for example.
However it is doing that by taking the lowest date in column K. That date can either be a monday, a sunday or any other day.
Our weeks (at least in this file) go from wednesday till wednesday since we pay our invoices on Wednesday.
Is there some magic formula for column A that will go from 01012020 to all the incoming weeks, without showing me weeks that we have already passed. So I only want to see future weeks!
I am sorry if I am a bit vague.
Thanks in advance
Ram
the excel file: https://easyupload.io/0py3zd