Automatically update dates

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
255
Office Version
  1. 2019
Platform
  1. 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:
1601473755711.png

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
 

Some videos you may like

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
Joined
Sep 25, 2018
Messages
255
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Sep 25, 2018
Messages
255
Office Version
  1. 2019
Platform
  1. Windows
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 AColumn K
30/09/202027/10/2020
07/10/202001/10/2020
14/10/202027/09/2020
21/10/202017/10/2020
28/10/202023/10/2020
04/11/202015/10/2020
this list keeps going onThis 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,295
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,295
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,337
Messages
5,547,355
Members
410,785
Latest member
phillippaige
Top