VB for Date

funkykayli

Board Regular
Joined
Apr 25, 2007
Messages
183
I have a table in Access with a field that is a date of loss. I want a vb code that will basically put the dates into categories (in a new field). So if the date is between 10/1/2005 and 10/1/2006 then in the new field it should say 10/1/2005. If the dates are between 10/1/2006 and 9/1/2007 then in the new field it should say 10/1/2006. If the dates are between 9/1/2007 and 9/1/2008 then in the new field it should say 9/1/2007. So on and so on. I had some code from Mr Excel that did this but it is only if the dates are constant, for example 10/1/xx - 10/1/xx and not 10/1/xx - 9/1/xx. Can this code be modified?

Dim intMonth As Integer
Dim intDay As Integer
Dim intYear As Integer

intMonth = Month(dteInput)
intDay = Day(dteInput)
intYear = Year(dteInput)

Select Case intMonth
Case 1
intYear = intYear - 1
Case 1
Select Case intDay
Case 1
intYear = intYear - 1
Case Else
intYear = intYear
End Select
Case 3 To 12
intYear = intYear
End Select
GetPolicyYear = DateSerial(intYear, 2, 1)
End Function
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Something like:
Iif(Month([dteinput])>=10,DateSerial(Year([dteinput],10,1),Dateserial(Year([dteinput])-1,10,1))

Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,132,678
Messages
5,654,709
Members
418,149
Latest member
tjanok

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top