# Create financial month

#### Gerrit.B

##### Board Regular
I have a table with date. With an query i calaculate all others like year, week, day, etc....
How can i create financial months like 4 weeks, 4 weeks, 5 weeks, 4 weeks, 4 weeks, 5 weeks,4 weeks, 4 weeks, 5 weeks,4 weeks, 4 weeks, 5 weeks where week 53 belongs to week 1 of next year.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi

Depends on what you want to do with them once you've created them to be honest. You could put together a Function to return a 'financial month' depending on the date passed to it and use the value returned in queries etc (see below for an idea on this) but like I say - it really depends on what you're trying to achieve.

Anyway, you could have a function to work out what the financial month is for a given date like this:

Code:
``````Public Function FinancialMonth(myDate as Date) As Integer
Select Case DatePart("ww",myDate)
Case 1 to 4:
FinancialMonth = 1
Case 5 to 10:
FinancialMonth = 2
.....etc etc
..
..
..
End Case
End Function``````
This would (once you've written it fully) return the financial month that you designate via the code depending on what week number it is in the year. This value could then be used in your selects etc
Code:
``(e.g. Select * from myTable Where DatePart("m",myDate) = FinancialMonth("01/Feb/2005")``

Hope that helps you.

Martin

Code:

************************************************

Public Function FinancialMonth(myDate As Date) As Integer
Select Case DatePart("ww", myDate)

Case 1 To 4:
FinancialMonth = 1
Case 5 To 8:
FinancialMonth = 2
Case 9 To 13:
FinancialMonth = 3
Case 14 To 17:
FinancialMonth = 4
Case 18 To 21:
FinancialMonth = 5
Case 22 To 26:
FinancialMonth = 6
Case 27 To 30:
FinancialMonth = 7
Case 31 To 34:
FinancialMonth = 8
Case 35 To 39:
FinancialMonth = 9
Case 40 To 43:
FinancialMonth = 10
Case 44 To 47:
FinancialMonth = 11
Case 48 To 52:
FinancialMonth = 12
Case 53 To 53:
FinancialMonth = 1
End Select

End Function

************************************************

Thanks, This Works Great.

Had to changed "End Case" to "End Select"

Yes - sorry about that, I realised after I'd hit the "Post" button but thought you'd spot it anyway.

Martin

Can this code below changed from date to weight.
example:
0 to 1 kg = 1
1 to 5 kg = 2
etc.

Code:

************************************************

Public Function FinancialMonth(myDate As Date) As Integer
Select Case DatePart("ww", myDate)

Case 1 To 4:
FinancialMonth = 1
Case 5 To 8:
FinancialMonth = 2
Case 9 To 13:
FinancialMonth = 3
Case 14 To 17:
FinancialMonth = 4
Case 18 To 21:
FinancialMonth = 5
Case 22 To 26:
FinancialMonth = 6
Case 27 To 30:
FinancialMonth = 7
Case 31 To 34:
FinancialMonth = 8
Case 35 To 39:
FinancialMonth = 9
Case 40 To 43:
FinancialMonth = 10
Case 44 To 47:
FinancialMonth = 11
Case 48 To 52:
FinancialMonth = 12
Case 53 To 53:
FinancialMonth = 1
End Select

End Function

************************************************

Yes - just use the same method replacing the "Case xxx" etc accordingly.

Martin

Public Function FinancialMonth(myDate As Date) As Integer
Select Case DatePart("ww", myDate)

What should i change in part above.

Hi

Where you've got
Public Function FinancialMonth(myDate As Date) As Integer
Select Case DatePart("ww", myDate)

at the moment, change it to something like

Public Function WeightFunction(myWeight As Integer) As Integer
Select Case myWeight
Case 0 to 1:
WeightFunction=1
Case 1-5:
WeightFunction=2
Case 6:
WeightFunction=3
etc etc..

HTH
Martin

Replies
2
Views
143
Replies
4
Views
263
Replies
6
Views
341
Replies
1
Views
139
Replies
1
Views
187

1,203,756
Messages
6,057,161
Members
444,908
Latest member
Jayrey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back