# Automatically update dates

#### Ramballah

##### Active Member
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.

Ram

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Ramballah

##### Active Member
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.

bump

#### Ramballah

##### Active Member
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.

Ram

#### Fluff

##### MrExcel MVP, Moderator

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

#### Ramballah

##### Active Member
Excel Formula:
``=MIN(K2:K7)+7-WEEKDAY(MIN(K2:K7)+3)``
Wow such a simple answer. Thanks this works like a charm!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
106
Replies
7
Views
157
Replies
3
Views
47
Replies
5
Views
86
Replies
1
Views
94