Add week commencing dates from choosing start of financial year date

[dlee83]

Hi,

I'm building a spreadsheet for financial data. Entering the data into the form with cells assigned to the dates is working fine. But I want to strip the sheet right back so that the user can determine which year/financial year the sheet starts with.

So, for example, the records for receipts need to start every Monday from 29/05/23, what i need the sheet to then display is the date of every Monday for the next 52 weeks. (across a row)

How can i get the user to just simply select a date on request, to then populate a sheet with all the relevant dates for the forthcoming year??

Any help is appreciated, any simple ideas I can convert to my code. Thanks in advance

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	29/05/2023	29/05/2023	05/06/2023	12/06/2023	19/06/2023	26/06/2023	03/07/2023	10/07/2023	17/07/2023	24/07/2023	31/07/2023
Main

Cell Formulas
Range	Formula
B1:BA1	B1	=WORKDAY.INTL(A1-1,SEQUENCE(,52),"0111111")
Dynamic array formulas.

I've only shown the first few dates, but you will get 52

 

[dlee83]

Sorry fluff, I'm looking for something in vba, so maybe a userform or combobox to select the year that then populates the row?

 

[Fluff]

For a combo on a userform you could use

Private Sub UserForm_Initialize()
   Me.ComboBox1.List = [sequence(10,,2020)]
End Sub

 

[dlee83]

For a combo on a userform you could use

Private Sub UserForm_Initialize()
   Me.ComboBox1.List = [sequence(10,,2020)]
End Sub

Fab, that's great.
So, now I've captured the year, how do i capture the first "Monday" of their financial year and display the commencing 52 weeks?

 

[Fluff]

How about

Private Sub CommandButton1_Click()
   Range("B2").Resize(, 52).Value = Evaluate("WORKDAY.INTL(date(" & Me.ComboBox1 & ",1,1) -1,SEQUENCE(,52),""0111111"")")
End Sub

 

[dlee83]

How about

Private Sub CommandButton1_Click()
   Range("B2").Resize(, 52).Value = Evaluate("WORKDAY.INTL(date(" & Me.ComboBox1 & ",1,1) -1,SEQUENCE(,52),""0111111"")")
End Sub

Since I formatted the cells, that puts all the Monday's dates in the row. Excellent! Thank you.
One more thing, if possible. How can we select the first date of, say, a financial year that may start on the first Monday in April and not necessarily the first Monday in January?

Thanks again

 

[Fluff]

You can change the 1st 1 after the " & Me.ComboBox1 & " to 4 for April

 

[dlee83]

You can change the 1st 1 after the " & Me.ComboBox1 & " to 4 for April

That's Brilliant, thank you

 

[Fluff]

You're welcome & thanks for the feedback.