PDA

View Full Version : Replacing Date with Period Based on Matrix


polishfc
10-30-2019, 08:13 AM
Hi All,

I am currently writing a macro to move a bunch of data from one sheet to another. I added the following code to add a date 3 months from the current day to column J if the cell is blank (this meaning that this is a new entry):


Set ws = Worksheets(“Sheet 1”)

If ws.Cells(i, 10) = "" _
Then

With ws.Cells(i, 10)
ws.Cells(i, 10).value = Date
ws.Cells(i, 10).NumberFormat = "mm/dd/yy"
End With

ws.Cells(i, 10).value = DateAdd("m", 3, CDate(ws.Cells(i, 10)))

End If

While the date code seems to work I would like to implement more code to replace the date that was inputted to a certain period based on list/matrix that I have in Sheet 2 in the range (A33:B98).

Example of what the list/matrix looks:


Period End Date

Period



5/1/2019

P01



5/5/2019

P01



5/12/2019

P01



5/19/2019

P01



5/26/2019

P01



5/27/2019

P02



6/3/2019

P02



6/10/2019

P02



6/17/2019

P02



6/24/2019

P02




















I’m imaging the code to run if then statement, where the macro will look at the ranges for each period and if falls between the two dates, then the date gets replaced with the respective period.

Example:

I run the macro on 3/11/2019, the date populated should be 6/11/2019. Macro looks through the table and sees that 6/11/2019 falls on a date greater than or equal to a date of 5/27/2019 and less than or equal to a date of 6/24/2019, therefore, the macro replaces 6/11/2019 with P02.

If it makes it easier column K (11th column) is open so the calcs could be done in that column and then pasted into column K.

I appreciate any guidance and help with this problem!

Please let me know if you need any additional info.
SamT
10-30-2019, 11:30 AM
Yeah... hmmmn.

VLookUp can't work because you need to return the larger date and VLookUp returns the lesser value.

Your sample code uses today's date, but your sample Period table doesn't
your sample table changes from Sundays to Mondays part way down.
Your sample table starts in the middle of the year. (not a deal killer, but still...)

If there was a true Pattern to the periods and days of the week a User Defined Function would work and your code could be as simple as

With ws.Cells(i, 10)
If .Value = "" then
.Value = GetPeriod(Some_Date_Here)
ElseIf IsDate(.Value) Then
.Value = GetPeriod(.Value)
End If
End With plus, of course, the actual GetPeriod Function. With a true Pattern, GetPeriod would work for any date of any year.

As you have posted, you will probably need some code to parse the table on sheet 2, and for that, we will need to see that entire table
Paul_Hossler
10-30-2019, 01:00 PM
You can try Match()

Wasn't sure about the destination of the period Pxx




Option Explicit


Sub test()
Dim ws As Worksheet, wsPeriods As Worksheet, wsDest As Worksheet
Dim rPeriods As Range
Dim vDate As Variant, vPeriod As Variant
Dim i As Long, n As Long




Set ws = Worksheets("Sheet1")
Set wsPeriods = Worksheets("Sheet2")
Set wsDest = Worksheets("Sheet3")

Set rPeriods = wsPeriods.Cells(34, 1)
Set rPeriods = Range(rPeriods, rPeriods.End(xlDown)).Resize(, 2)

With Application.WorksheetFunction
vDate = .Transpose(rPeriods.Columns(1).Value)
vPeriod = .Transpose(rPeriods.Columns(2).Value)
End With

For i = LBound(vDate) To UBound(vDate)
vDate(i) = CLng(CDate(vDate(i)))
Next i

For i = 1 To 100
With ws.Cells(i, 10)
n = 0
On Error Resume Next
n = Application.WorksheetFunction.Match(CLng(.Value), vDate, 1)
On Error GoTo 0

If n = 0 Then
wsDest.Cells(i, 10).Value = "Nope"

Else
wsDest.Cells(i, 10).Value = vPeriod(n)
End If
End With
Next i


End Sub
polishfc
10-30-2019, 01:02 PM
Yeah... hmmmn.

VLookUp can't work because you need to return the larger date and VLookUp returns the lesser value.

Your sample code uses today's date, but your sample Period table doesn't
your sample table changes from Sundays to Mondays part way down.
Your sample table starts in the middle of the year. (not a deal killer, but still...)

If there was a true Pattern to the periods and days of the week a User Defined Function would work and your code could be as simple as

With ws.Cells(i, 10)
If .Value = "" then
.Value = GetPeriod(Some_Date_Here)
ElseIf IsDate(.Value) Then
.Value = GetPeriod(.Value)
End If
End With plus, of course, the actual GetPeriod Function. With a true Pattern, GetPeriod would work for any date of any year.

As you have posted, you will probably need some code to parse the table on sheet 2, and for that, we will need to see that entire table

Thanks for your response SamT.

To start off, apologies I ended posting an incorrect and partial table. The periods are essentially one month cycles with a few deviations here or there (i.e. Jan and Feb). Below please find a complete table:



Period End Date

Period



4/1/2019

P04 (April)



4/5/2019

P04 (April)



4/12/2019

P04 (April)



4/19/2019

P04 (April)



4/26/2019

P04 (April)



4/27/2019

P05 (May)



5/3/2019

P05 (May)



5/10/2019

P05 (May)



5/17/2019

P05 (May)



5/24/2019

P05 (May)



5/25/2019

P06 (June)



5/31/2019

P06 (June)



6/7/2019

P06 (June)



6/14/2019

P06 (June)



6/21/2019

P06 (June)



6/28/2019

P06 (June)



6/29/2019

P07 (July)



7/5/2019

P07 (July)



7/12/2019

P07 (July)



7/19/2019

P07 (July)



7/26/2019

P07 (July)



7/27/2019

P08 (August)



8/2/2019

P08 (August)



8/9/2019

P08 (August)



8/16/2019

P08 (August)



8/23/2019

P08 (August)



8/24/2019

P09 (September)



8/30/2019

P09 (September)



9/6/2019

P09 (September)



9/13/2019

P09 (September)



9/20/2019

P09 (September)



9/27/2019

P09 (September)



9/28/2019

P10 (October)



10/4/2019

P10 (October)



10/11/2019

P10 (October)



10/18/2019

P10 (October)



10/25/2019

P10 (October)



10/26/2019

P11 (November)



11/1/2019

P11 (November)



11/8/2019

P11 (November)



11/15/2019

P11 (November)



11/22/2019

P11 (November)



11/23/2019

P12 (December)



11/29/2019

P12 (December)



12/6/2019

P12 (December)



12/13/2019

P12 (December)



12/20/2019

P12 (December)



12/27/2019

P12 (December)



12/28/2019

P01 (January)



1/3/2020

P01 (January)



1/10/2020

P01 (January)



1/17/2020

P01 (January)



1/24/2020

P01 (January)



1/25/2020

P02 (February)



1/31/2020

P02 (February)



2/7/2020

P02 (February)



2/14/2020

P02 (February)



2/21/2020

P02 (February)



2/22/2020

P03 (March)



2/28/2020

P03 (March)



3/6/2020

P03 (March)



3/13/2020

P03 (March)



3/20/2020

P03 (March)



3/27/2020

P03 (March)



3/31/2020

P03 (March)





Hopefully the correct and full table helps clarify some of the confusion.

Would appreciate any help on this, trying to figure this one out for a while now.
Paul_Hossler
10-30-2019, 01:31 PM
with your complete lookup table
SamT
10-30-2019, 03:56 PM
4/1/2019
P04 (April)
Mon


4/5/2019
P04 (April)
Fri


4/12/2019
P04 (April)
Fri


4/19/2019
P04 (April)
Fri


4/26/2019
P04 (April)
Fri


4/27/2019
P05 (May)
Sat


5/3/2019
P05 (May)
Fri


5/10/2019
P05 (May)
Fri


5/17/2019
P05 (May)
Fri


5/24/2019
P05 (May)
Fri


5/25/2019
P06 (June)
Sat


5/31/2019
P06 (June)
Fri


6/7/2019
P06 (June)
Fri


6/14/2019
P06 (June)
Fri


6/21/2019
P06 (June)
Fri


6/28/2019
P06 (June)
Fri


6/29/2019
P07 (July)
Sat


7/5/2019
P07 (July)
Fri


7/12/2019
P07 (July)
Fri


7/19/2019
P07 (July)
Fri


7/26/2019
P07 (July)
Fri


7/27/2019
P08 (August)
Sat


8/2/2019
P08 (August)
Fri


8/9/2019
P08 (August)
Fri


8/16/2019
P08 (August)
Fri


8/23/2019
P08 (August)
Fri


8/24/2019
P09 (September)
Sat


8/30/2019
P09 (September)
Fri


9/6/2019
P09 (September)
Fri


9/13/2019
P09 (September)
Fri


9/20/2019
P09 (September)
Fri


9/27/2019
P09 (September)
Fri


9/28/2019
P10 (October)
Sat


10/4/2019
P10 (October)
Fri


10/11/2019
P10 (October)
Fri


10/18/2019
P10 (October)
Fri


10/25/2019
P10 (October)
Fri


10/26/2019
P11 (November)
Sat


11/1/2019
P11 (November)
Fri


11/8/2019
P11 (November)
Fri


11/15/2019
P11 (November)
Fri


11/22/2019
P11 (November)
Fri


11/23/2019
P12 (December)
Sat


11/29/2019
P12 (December)
Fri


12/6/2019
P12 (December)
Fri


12/13/2019
P12 (December)
Fri


12/20/2019
P12 (December)
Fri


12/27/2019
P12 (December)
Fri


12/28/2019
P01 (January)
Sat


1/3/2020
P01 (January)
Fri


1/10/2020
P01 (January)
Fri


1/17/2020
P01 (January)
Fri


1/24/2020
P01 (January)
Fri


1/25/2020
P02 (February)
Sat


1/31/2020
P02 (February)
Fri


2/7/2020
P02 (February)
Fri


2/14/2020
P02 (February)
Fri


2/21/2020
P02 (February)
Fri


2/22/2020
P03 (March)
Sat


2/28/2020
P03 (March)
Fri


3/6/2020
P03 (March)
Fri


3/13/2020
P03 (March)
Fri


3/20/2020
P03 (March)
Fri


3/27/2020
P03 (March)
Fri


3/31/2020
P03 (March)
Tue
SamT
10-30-2019, 05:11 PM
I added the day of the week to your table in my previous post.

Studying that, I surmise that your Fiscal year runs from Apr 1st to Mar 31st.

The Business Rules seem to be:


The Period Number is the same as the Month's calendar month number. ie Jan = P01, Dec = P12
If the last day of a month is not Friday, then the week of the last Saturday of the month shall be in the next period.
Regardless of the above, the first day of the Fiscal year shall be in Period P04
Regardless of the above, the last day of the Fiscal year shall be in period P03


Given a complete set of Business Rules. as above. one can write a perpetual function that works across many years.

However, I am lazy, so here is a simpler Function that you will need to edit each fiscal year
Option Explicit

'Listing the last dates per period here makes it easy to change the dates every year
'Double check these dates, I ternd towrds erros.
'Note: these are the last dates of the periods.
Enum EndOfPeriodDate
eopP04 = CDate("4/26/2019")
eopP05 = CDate("5/24/2019")
eopP06 = CDate("6/28/2019")
eopP07 = CDate("7/26/2019")
eopP08 = CDate("8/23/2019")
eopP09 = CDate("9/27/2019")
eopP10 = CDate("10/25/2019")
eopP11 = CDate("11/22/2019")
eopP12 = CDate("12/27/2019")
eopP01 = CDate("1/24/2020")
eopP02 = CDate("2/21/2020")
eopP03 = CDate("3/31/2020")
End Enum


Function GetPeriod(Optional Dte As Date) As String
'If a date is not provided, today's Date will be used

If Dte = 0 Then Dte = Date

'Note: These must be in date Order, not Period number order
Select Case Dte
Case Is <= eopP04: GetPeriod = "P04"
Case Is <= eopP05: GetPeriod = "P05"
Case Is <= eopP06: GetPeriod = "P06"
Case Is <= eopP07: GetPeriod = "P07"
Case Is <= eopP08: GetPeriod = "P08"
Case Is <= eopP09: GetPeriod = "P09"
Case Is <= eopP10: GetPeriod = "P10"
Case Is <= eopP11: GetPeriod = "P11"
Case Is <= eopP12: GetPeriod = "P12"
Case Is <= eopP01: GetPeriod = "P01"
Case Is <= eopP02: GetPeriod = "P02"
Case Is <= eopP03: GetPeriod = "P03"
Case Else: GetPeriod = "Error"
End Select
End Function

Now you can use this in your code

With ws.Cells(i, 10)
If .Value = "" then
.Value = GetPeriod
ElseIf IsDate(.Value) Then
.Value = GetPeriod(.Value)
End If
End With

You can also use Range("??") = GetPeriod(Some_Date_here) with any Some_Date_here in the encoded Fiscal year
Paul_Hossler
10-30-2019, 09:11 PM
1. It looks like you have a holiday schedule incorporated into your fiscal calendar

11/23/2019 rolls to P12 over the Thanksgiving break

12/20/2019 rolls to P01 over the holidays

Correct?

2. I think 3/31/2020 (last Tue of month) should be P04, not P03 as in your example

3. Then using SamT's business rules, this seems to get your answers




Option Explicit


Function DateToPeriod(DT As Date) As String
Dim m As Long, d As Long, y As Long, w As Long, p As Long
Dim dtLastSat As Date


m = Month(DT)
d = Day(DT)
y = Year(DT)
w = Weekday(DT) ' Sunday = 1, ... , Saturday = 7


dtLastSat = DateSerial(y, m + 1, 0)

If m = 2 Or m = 8 Or m = 11 Then dtLastSat = dtLastSat - 7

Do While Weekday(dtLastSat) <> vbSaturday
dtLastSat = dtLastSat - 1
Loop

If d >= Day(dtLastSat) Then
p = m + 1
If p = 13 Then p = 1
Else
p = m
End If




DateToPeriod = "P" & Format(p, "00")

End Function



However, I think you could get by with just a VLookup formula where A:B is the start date of each period. Manually maintained true but an option

25354