Automatically update dates

[Ramballah]

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 01-01-2020 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

 

[Ramballah]

bump,
I should clarify that our real weeks don't go from wednesday to wednesday, but we mostly use the file on wednesdays. So I just want to have the dates in column A to go from wednesday to wednesday.

 

[Ramballah]

bump

 

[Ramballah]

Hi all,

This is a bump but i'll clarify it.

What I want in column A is to see the dates from every Wednesday however:
In column K if the minimum date is past the wednesday then I want the next wednesday to show up
So:

Column A	Column K
30/09/2020	27/10/2020
07/10/2020	01/10/2020
14/10/2020	27/09/2020
21/10/2020	17/10/2020
28/10/2020	23/10/2020
04/11/2020	15/10/2020
this list keeps going on	This column exists of random dates

So I want in column A to get the first following Wednesday that comes after the min date in column K so the min date is 27/09 and the first Wednesday will be 30/09/2020.
If 15/10/2020 was the min date then it would be A2=21/10/2020
From A3 and further its just A2+7 but I need to get a formula for A2.
If you need more information please let me know.

Thanks in advance,
Ram

 

[Fluff]

How about

=MIN(K2:K7)+7-WEEKDAY(MIN(K2:K7)+3)

 

[Ramballah]

How about

=MIN(K2:K7)+7-WEEKDAY(MIN(K2:K7)+3)

Wow such a simple answer. Thanks this works like a charm!

 

[Fluff]

You're welcome & thanks for the feedback.