Split years into weeks and get a date

Hi,

A situation: A meeting have been convening since 17-Sep-1992 on weekly basis.

How can we get next meeting exact date with formula AND VBA as there may have been difference due to leap years.

Note: Meeting was never missed on every 7th day. So how can get a list of all the previous meetings as well

I am expecting a single line code or formula and not loops

As dates as stored just by counting days since first date of Ecvel calendar (either Jan 1900 or 1904 - depending on settings) and meetings are organized weekly (say every Thursday noon), just 7 could be added to have date of next meeting or substaracted to have a previous meeting date.

So to have the upcoming meeting you can try formula:

Formula:

=ROUNDUP((TODAY()-A1)/7,0)*7+A1

if A1 is a date of first meeting

IN VBA it could be:

Sub foo()
Const firstdate = #9/17/1992#
MsgBox "Upcoming meeting: " & Format(WorksheetFunction.RoundUp((Date - firstdate) / 7, 0) * 7 + firstdate, "dd MMMM yyyy")
End Sub

Thanks kasper
It is giving the next date. if the meeting is after 2 weeks do I just need to replace 7 with 14?

And the other part of my question please

So how can get a list of all the previous meetings as well

If, as you say, you have had a meeting every week exactly seven days after the previous meeting, I fail to see why leap years would have affected anything.

I would say that the starting date in A1 and the this formula in A2 copied down should work: =A1+7

Excel 2016 (Windows) 32 bit

	A
1	17/09/1992
2	24/09/1992
3	01/10/1992
4	08/10/1992
5	15/10/1992
6	22/10/1992
7	29/10/1992
8	05/11/1992
9	12/11/1992
10	19/11/1992
11	26/11/1992
12	03/12/1992
13	10/12/1992
14	17/12/1992
15	24/12/1992
16	31/12/1992
17	07/01/1993
18	14/01/1993
19	21/01/1993
20	28/01/1993
21	04/02/1993
22	11/02/1993
23	18/02/1993
24	25/02/1993
25	04/03/1993
26	11/03/1993
27	18/03/1993
28	25/03/1993
29	01/04/1993
30	08/04/1993
31	15/04/1993
32	22/04/1993
33	29/04/1993
34	06/05/1993
35	13/05/1993
36	20/05/1993
37	27/05/1993
38	03/06/1993

Sheet: Sheet1

Yes. You are right Ali. Its just because of fear of working with dates.

Can we get this huge list of dates with a single line VBA code? as I will need them in an access mini project.

Alas, I have no idea about VBA Imran, I am afraid.

Opps. the second part was for other members who are VBA users. I know you don't that's why I had separated these 2 lines in post#5.

It should have not embarrassed those who don't know VBA

Or may be current meeting date and 10 previous meetings and 10 upcoming:

Sub foo3()
Const firstdate = #9/17/1992#
Dim myarray(1 To 21) As Date, nextmeeting As Date, i As Long

nextmeeting = WorksheetFunction.RoundUp((Date - firstdate) / 7, 0) * 7 + firstdate
For i = -10 To 10
  myarray(i + 11) = nextmeeting + i * 7
Next i
'here you have data ready - let's output it
Range("J1:J21") = Application.Transpose(myarray)
Range("J1:J21").NumberFormat = "dd MMM yyyy"
End Sub

I'm not embarrassed, don't worry! For the few occasions I have needed to use VBA, I have been lucky enough to get excellent guidance here, but I've never needed it enough to learn it.

Of course with VBA you can "produce" data structure filled with dates.
let's say you want a vector (1D array - a list) of all dates since the startdate till today+1 year ahead

Sub foo2()
Const firstdate = #9/17/1992#
Dim myarray() As Date, entries As Long, i As Long

entries = WorksheetFunction.RoundUp((DateSerial(Year(Date) + 1, Month(Date), Day(Date)) - firstdate) / 7, 0)
ReDim myarray(1 To entries)
For i = 1 To entries
  myarray(i) = firstdate + (i - 1) * 7
Next i
'here you have data ready - let's output it
Range("J1:J" & entries) = Application.Transpose(myarray)
Range("J1:J" & entries).NumberFormat = "dd MMMM yyyy"
End Sub

Thanks kaper as I have mentioned loop should not be used

here is an example but I am unable to adopt it for getting the dates for every 7th day till the next meeting date

Sub test()
Dim a
a = [index(text(date(1992,row(1:365),1),"dd/mm/yyyy"),)]
End Sub

The level of this page is very advance but something like this is needed.

Originally Posted by jindon

Do you mean?

a = [index(text(date(1992,1,17)+(row(1:100)-1)*7,"dd/mm/yyyy"),)]

Simple answer Yes

Solved.

Why no loop?
if you want to use it as a source for listbox, then this (not tested - writing from a smartphone) shall work:

Sub foo3()
Const firstdate = #9/17/1992#
Dim myarray(0 To 20) As string, nextmeeting As Date, i As Long

nextmeeting = WorksheetFunction.RoundUp((Date - firstdate) / 7, 0) * 7 + firstdate
For i = -10 To 10
  myarray(i + 10) = format(nextmeeting + i * 7,"dd MMM yyyy")
Next i
a = myarray
End Sub

But of course it's longer than simple formula evaluation:

a = [index(text(date(1992,1,17)+(row(1:100)-1)*7,"dd/mm/yyyy"),)]

Yes I adopted the second one. Thanks kaper