Enter a date, find the period..?

[towners]

Hi all,

I'me entering a date and want to find the period from a set of data. Can anyone help?

ColA (period number) Col B (from date) Col C (to date)

Period 1; 3/12/10; 31/12/10
Period 2; 3/1/11; 28/1/11
Period 3; 3/2/11; 28/2/11

So I want to put some code into my userform VBA that takes the date and find the period to then add to my worksheet.

i.e. date = 5/12/10, Period = 1

Any help gratefully received...

Thanks

Paul

 

[MickG]

If you have "TextBox1 " to enter your Date & TextBox2 to receive the result.
Then Place this in a Commandbutton on the form.

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim Dn As Range
Dim dt As Date
Dim Fd As Boolean
dt = TextBox1
Set Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
For Each Dn In Rng
    If Dn(, 2) <= dt And Dn(, 3) >= dt Then
        TextBox2 = Dn
        Fd = True
         Exit For
    End If
Next Dn
If Fd = False Then TextBox2 = "No Match Found"
End Sub

Regards Mick

 

[towners]

That's magic that is Mick. Thank you very much.

 

[towners]

Any ideas how I can also get the same result in a worksheet?

Regards

Paul

 

[Haldoug]

Can the period 1 be December, period 2 be January, and period 3 be February, or does an error message have to occur if the 1,2,29,30, or 31 are the day?

 

[towners]

Hi,

No, the periods are accounting periods. There are 13 periods in a year each has 28 days so the from - to dates change each year...

 

[Haldoug]

If the date is in b5, then
=IF(MONTH(B5)=12,1,MONTH(B5)-1)

If you want to word period then
=if(month(b5)=12,"Period 1","Period "&month(b5-1)

 

[towners]

Hi,

Not sure that will work. I have a look up table over 3 columns: Period Number, From date, To date. I want to enter a date and have the worksheet check the look up table and populate the associated period in the cell next to my date.

 

[Haldoug]

If the first day the fiscal year is 12/3/2010, and each of the periods if for 28 days, and the date is in cell A3, then the period the date is in is =int((a3-41079)/28) + 1. This subtract 12/3/2010 from the date, and finds out which 28 day period it is in. The integer function truncates the fraction, and adding one rounds up the result. 41079 is the numerical representation of 12/3/2010.

Hopefully I am understanding the situation right.

 

[Haldoug]

Whoops, the integer is suppose to be 40513.