Return Bi-Weekly Period Based on Transaction Date

[wannabewhiz]

Hi everyone Long time lurker, first time poster

My goal is to use a formula to return a date from an established list on another sheet based on a different date. Let me explain..

"Sheet 1" has been populated with a long list of Bi-Weekly Period End dates, specific to my company. The first "Bi-Weekly Period End Date" is static & the list continues using a simple "A1+14" formula. Because they are specific to my company and not just every 2 weeks starting 1/1/xx, this can not change. I can add columns in, but reinventing how these dates are populated is unfortunately not a viable option.

"Sheet 2" is where all 'hard' data for the file is housed and I use formulas to extract what is needed for pivot tables to make sense of the large data sets. Within the data are transaction dates embedded in column E for each row and I use a formula to pull out just the date for each row into "M1", "M2", etc.

What formula could I use in say "AF1" on "Sheet 2" to return the correct "Bi-Weekly Period End Date" from "Sheet 1" that the transaction date in "M1" falls within?

The Vlookups I've tried don't return consistent results as the "M1" date doesn't 'match' many of the Bi-Weekly Period End dates. Some internet sleuthing gives me a lot of results for pay dates based on 'today', but nothing remotely applicable to this quandary. I'm hesitant to upload images or Mini-sheets as the data I'm working with is confidential, but can answer any questions as best I can to get some help with this. I'm stumped!!

Thank you ?
wannabewhiz

 

[BSALV]

vlookup with 1 as last parameter ???

amoverton2 - 1 (1).xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	in your sheet1							in your sheet2
2	Biweekly	period						your date	biweekly	period
3	zo 02/jan/22	period1						zo 02/jan/22	zo 02/jan/22	period1
4	zo 16/jan/22	period2						ma 03/jan/22	zo 02/jan/22	period1
5	zo 30/jan/22	period3						di 04/jan/22	zo 02/jan/22	period1
6	zo 13/feb/22	period4						wo 05/jan/22	zo 02/jan/22	period1
7	zo 27/feb/22	period5						do 06/jan/22	zo 02/jan/22	period1
8	zo 13/mrt/22	period6						vr 07/jan/22	zo 02/jan/22	period1
9	zo 27/mrt/22	period7						za 08/jan/22	zo 02/jan/22	period1
10	zo 10/apr/22	period8						zo 09/jan/22	zo 02/jan/22	period1
11	zo 24/apr/22	period9						ma 10/jan/22	zo 02/jan/22	period1
12	zo 08/mei/22	period10						di 11/jan/22	zo 02/jan/22	period1
13	zo 22/mei/22	period11						wo 12/jan/22	zo 02/jan/22	period1
14	zo 05/jun/22	period12						do 13/jan/22	zo 02/jan/22	period1
15	zo 19/jun/22	period13						vr 14/jan/22	zo 02/jan/22	period1
16	zo 03/jul/22	period14						za 15/jan/22	zo 02/jan/22	period1
17	zo 17/jul/22	period15						zo 16/jan/22	zo 16/jan/22	period2
18	zo 31/jul/22	period16						ma 17/jan/22	zo 16/jan/22	period2
19	zo 14/aug/22	period17						di 18/jan/22	zo 16/jan/22	period2
20	zo 28/aug/22	period18						wo 19/jan/22	zo 16/jan/22	period2
21	zo 11/sep/22	period19						do 20/jan/22	zo 16/jan/22	period2
22	zo 25/sep/22	period20						vr 21/jan/22	zo 16/jan/22	period2
23	zo 09/okt/22	period21						za 22/jan/22	zo 16/jan/22	period2
24	zo 23/okt/22	period22						zo 23/jan/22	zo 16/jan/22	period2
25	zo 06/nov/22	period23						ma 24/jan/22	zo 16/jan/22	period2
26	zo 20/nov/22	period24						di 25/jan/22	zo 16/jan/22	period2
27	zo 04/dec/22	period25						wo 26/jan/22	zo 16/jan/22	period2
28	zo 18/dec/22	period26						do 27/jan/22	zo 16/jan/22	period2
29	zo 01/jan/23	period27						vr 28/jan/22	zo 16/jan/22	period2
30	zo 15/jan/23	period28						za 29/jan/22	zo 16/jan/22	period2
31	zo 29/jan/23	period29						zo 30/jan/22	zo 30/jan/22	period3
32	zo 12/feb/23	period30						ma 31/jan/22	zo 30/jan/22	period3
33	zo 26/feb/23	period31						di 01/feb/22	zo 30/jan/22	period3
34	zo 12/mrt/23	period32						wo 02/feb/22	zo 30/jan/22	period3
35	zo 26/mrt/23	period33						do 03/feb/22	zo 30/jan/22	period3
36	zo 09/apr/23	period34						vr 04/feb/22	zo 30/jan/22	period3
37	zo 23/apr/23	period35						za 05/feb/22	zo 30/jan/22	period3
38								zo 06/feb/22	zo 30/jan/22	period3
39								ma 07/feb/22	zo 30/jan/22	period3
40
Blad1

Cell Formulas
Range	Formula
I3:I39	I3	=VLOOKUP(H3,$A$3:$B$37,1,1)
J3:J39	J3	=VLOOKUP(I3,$A$3:$B$37,2,1)
H4:H39	H4	=+H3+1
A4:A37	A4	=+A3+14

 

[wannabewhiz]

vlookup with 1 as last parameter ???

I'm not sure I follow this.

When using this formula: "=VLOOKUP(H25,$A$3:$B$37,1,1)", ensuring the table array is referencing the bi-weekly periods in my workbook, "N/A" results are returned.

In review of the J3 formula, it doesn't appear the I3 formulas are dependent on adding the "period1, period2, etc.", so I didn't add it (unnecessary data only inflates an already memory intensive workbook). Was this important?

 

[wannabewhiz]

vlookup with 1 as last parameter ???

Additionally, the returned data is incorrect.

For example: Jan 04, 2022 wouldn't be Bi-Weekly Period End Date Jan 02, 2022 because it falls after that period end date. It would be Period End Date Jan 16, 2022. Even if I was able to return similar results, unfortunately, it's not accurate.

 

[BSALV]

A3 is your first end of period date, i used sunday 2/1/22 there, but you have to change into a date that "is specific to your company". Once that is done, the other cells in that column are always +14.

if you want the enddate, add simply 13 (or 14 depends what you want)

=VLOOKUP(H3+13,$A$3:$B$37,1,1)