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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,503
Messages
5,511,701
Members
408,860
Latest member
RajaRavi

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top