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.
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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.