Payroll dates

[2077delta]

I want to create a list of all our pay check dates in a year sorted in the order in which they occur. We have two payrolls, one pays weekly every Friday and one pays semi-monthly on the 15th and last day of the month. Is it possibly for the date of both payrolls to occur on the same day. Can I do this with just one formula and copy it down or possibly and array formula that automatically creates the exact list as it can vary by one pay period from year to year. I would like to have the user enter the first day of the year in a cell then use this date to create the list. Finally since I'm making my wish list, if it's an array, can it have an adjacent column with either W for weekly (Friday) pay dates and S (the semi-monthly pay dates).

 

[LouisT]

I want to create a list of all our pay check dates in a year sorted in the order in which they occur. We have two payrolls, one pays weekly every Friday and one pays semi-monthly on the 15th and last day of the month. Is it possibly for the date of both payrolls to occur on the same day. Can I do this with just one formula and copy it down or possibly and array formula that automatically creates the exact list as it can vary by one pay period from year to year. I would like to have the user enter the first day of the year in a cell then use this date to create the list. Finally since I'm making my wish list, if it's an array, can it have an adjacent column with either W for weekly (Friday) pay dates and S (the semi-monthly pay dates).

Hey there; you ask is a bit confusing. you want One list that has both Weekly & Bi-Monthly payroll dates? and then the list changes each year? I assume just the bi-weekly dates change? Also for the bi-monthly, is it the 15 & last day of the month regardless if it falls on a weekend? i.e. -- Saturday is the 15th so is pay day on Friday the 14th? Sounds like you need 2 lists to me

 

[Dave Patton]

The suggestion uses Let which is available on the latest versions of Excel.
N.B. Some of the dates may fall on a weekend.

T202209a.xlsm
	B
1	01-01-22
2	Fri 07-Jan-22
3	Fri 14-Jan-22
4	Sat 15-Jan-22
5	Fri 21-Jan-22
6	Fri 28-Jan-22
7	Mon 31-Jan-22
8	Fri 04-Feb-22
9	Fri 11-Feb-22
10	Tue 15-Feb-22
11	Fri 18-Feb-22
12	Fri 25-Feb-22
13	Mon 28-Feb-22
14	Fri 04-Mar-22
15	Fri 11-Mar-22
16	Tue 15-Mar-22
17	Fri 18-Mar-22
18	Fri 25-Mar-22
19	Thu 31-Mar-22
20
1e

Cell Formulas
Range	Formula
B2:B19	B2	=LET(x,MIN(WORKDAY.INTL(B1,1,"1111011"),EOMONTH(B1+1,0)),IF(DAY(B1)<15,MIN(x,DATE(YEAR(B1),MONTH(B1),15)),x))

 

[Dave Patton]

T202209a.xlsm
	A	B	C	D
1		01-01-22
2		Fri 07-Jan-22	W	W
3		Fri 14-Jan-22	W	W
4		Sat 15-Jan-22	S	S
5		Fri 21-Jan-22	W	W
1e

Cell Formulas
Range	Formula
B2:B5	B2	=LET(x,MIN(WORKDAY.INTL(B1,1,"1111011"),EOMONTH(B1+1,0)),IF(DAY(B1)<15,MIN(x,DATE(YEAR(B1),MONTH(B1),15)),x))
C2:C5	C2	=IF(AND(WEEKDAY(B2,1)=6,OR(DAY(B2)=15,B2=EOMONTH(B2,0)))," WS ",IF(WEEKDAY(B2,1)=6," W "," S "))
D2:D5	D2	=LET(x,WEEKDAY(B2,1)=6,IF(AND(x,OR(DAY(B2)=15,B2=EOMONTH(B2,0)))," WS ",IF(x," W "," S ")))