Extraxt months

arcootje

Board Regular
Joined
Jun 2, 2004
Messages
110
Is the following possible:

START DATE: 03-01-2005
END DATE: 16-02-2006

Is it possible to get the following output:
START DATE END DATE
03-01-05 31-01-05
01-02-05 28-02-05
01-03-05 31-03-05
01-04-05 30-04-05
01-05-05 31-05-05
01-06-05 30-06-05
01-07-05 etc...

Thanx in advance ...
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi arcootje,

What do you need to do? It is possible to generate a list of dates like this, but whether or not you need to will depend on what outcome is required.

Also, if you do need to create this list, where does it need to end up?
In a table as a bunch of records
In the rowsource of a listbox or combo box
somewhere else...

Denis
 

arcootje

Board Regular
Joined
Jun 2, 2004
Messages
110
the output must come in a new table ...

orginal table headers:
CODE
START
END
VALUE

output contains the same headers, but the date must be split

thanx
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
OK, here is one way to do it.

If you have Access 2000 or higher, go into any code module then Tools > References and check the box next to Microsoft DAO 3.6 Object Library.

You will need:
1. Table (I called mine tblDates) with fields StartDate and EndDate. You can have other fields but they are not required for the code.

2. Unbound form with the following:
a. textbox called txtStart
b. textbox called txtEnd
c. Listbox called lstDates, which displays at least StartDate and EndDate from tblDates
d. Button

3. The code for the button's Click event is this --
Code:
    Dim dbs As DAO.database
    Dim rst As DAO.Recordset
    Dim dFirst As Date
    Dim dLast As Date
    Dim dRecStart As Date
    
    dFirst = Me.txtStart
    dLast = Me.txtEnd
    dRecStart = Me.txtStart
    
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("tblDates")
    With rst
        Do While dRecStart < dLast
            .AddNew
            !startdate = dRecStart
            If dLast < DateSerial(Year(dRecStart), Month(dRecStart) + 1, 0) Then
                !enddate = dLast
            Else
                !enddate = DateSerial(Year(dRecStart), Month(dRecStart) + 1, 0)
            End If
            .Update
            dRecStart = DateSerial(Year(dRecStart), Month(dRecStart) + 1, 1)
        Loop
    End With
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Me.lstDates.Requery
It does the following:
1. Creates the records
2. Requeries the listbox so you see the result.
Note: the code does NOT check that the records already exist before creating new ones. You will need to adjust table, field and control names to suit.

Denis
 

Forum statistics

Threads
1,137,366
Messages
5,681,068
Members
419,950
Latest member
BeckiJae

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