Formulas referencing Pay Dates in year

[banneduser123]

Hi,

I'm trying to build out this excel file to keep track of my finances.

I could use some help with several formulas revolving around the pay schedule of a year. Any help would be appreciated and I can try toying around.


I have a hard-coded list of all the pay dates in a year, for a bi-weekly schedule in column A of my Sheet).
(side note - is there a way this can be formulated as well? if not, i suppose i would just need formulas that reference this list)


1. I'd like to create 2 formulas, that look at what today's date is, and then tells me how many business days until the next pay day (and also how many calendar days)

2. I'd like to create 2 formulas, that gives me a count of how many pay dates are left in the month. (also how many left in the year)


this would really help me start up from other formulas i'd like to create...i just had no idea how these would work...

 

[banneduser123]

so what if i wanted to add this functionality:


basically, i'd like to know what my bank account balance is going to be at a certain point in time. does the below explain it well enough? (where the formula would go in cell B5

Book2
	A	B	C	D	E
1	Input Date	12/25/2020		Average Paycheck	$500
2
3	Current Bank Balance	$50
4
5	(# of paychecks * D5) + B2
6
Sheet1

**check that, i don't know what i'm doing

does this help? the yellow highlighted cell is what should tell me how many paychecks i'll be getting from the date that was inserted "Input Date" field, to Today
(Data!A:A references the list of dates we created earlier, previously column F)

financial planner.xlsx
	F	G	H	I	J
42			Input Date	Average Paycheck
43			1/15/2021	500
44	PCs left Nov	PCs left 2020
45	1	3
46	Expected Income	25	$ 12,500.00
2020

Cell Formulas
Range	Formula
F45	F45	=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&EOMONTH(TODAY(),0))
G45	G45	=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&DATEVALUE("31 dec"&YEAR(TODAY())))
G46	G46	=COUNTIFS(Data!A:A,">="&H43,Data!A:A,"<="&DATEVALUE("31 dec"&YEAR(H43)))
H46	H46	=I43*G46

 

[banneduser123]

**check that, i don't know what i'm doing

does this help? the yellow highlighted cell is what should tell me how many paychecks i'll be getting from the date that was inserted "Input Date" field, to Today
(Data!A:A references the list of dates we created earlier, previously column F)

financial planner.xlsx
	F	G	H	I	J
42			Input Date	Average Paycheck
43			1/15/2021	500
44	PCs left Nov	PCs left 2020
45	1	3
46	Expected Income	25	$ 12,500.00
2020

Cell Formulas
Range	Formula
F45	F45	=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&EOMONTH(TODAY(),0))
G45	G45	=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&DATEVALUE("31 dec"&YEAR(TODAY())))
G46	G46	=COUNTIFS(Data!A:A,">="&H43,Data!A:A,"<="&DATEVALUE("31 dec"&YEAR(H43)))
H46	H46	=I43*G46

i think i got it.

going to try using this:

=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&EOMONTH(H43,0))

 

[banneduser123]

i think i got it.

going to try using this:

=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&EOMONTH(H43,0))

this does the trick:

=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&H43)

 

[etaf]

seems you have answered all the questions now.
Note that using
">="&TODAY() will include Today and so on pay day will count that day , as it is greater than or equal to
so on the 27th Nov 2020 , which is a payday , it will still show 1 payday in month which includes 27th Nov
Just change
">="&TODAY()
to
">"&TODAY()
Now it will NOT include the payday and so will show zero

Payday-ETAF using H2.xlsx
	A	B	C	D	E	F	G	H
1	Business Days	Calendar Days	Pay Days in Month	Pay Days to year end		LOOKUP		DATE
2	1	0	1	3		12/24/21		11/27/20
3						12/10/21
4						11/26/21
5						11/12/21
6						10/29/21
7						10/15/21
8						10/1/21
9						9/17/21
10						9/3/21
11						8/20/21
12						8/6/21
13						7/23/21
14						7/9/21
15						6/25/21
16						6/11/21
17						5/28/21
18						5/14/21
19						4/30/21
20						4/16/21
21						4/2/21
22						3/19/21
23						3/5/21
24						2/19/21
25						2/5/21
26						1/22/21
27						1/8/21
28						12/25/20
29						12/11/20
30						11/27/20
31						11/13/20
32						10/30/20
33						10/16/20
Sheet1

Cell Formulas
Range	Formula
A2	A2	=NETWORKDAYS(H2,INDEX(F:F,MATCH(H2,F:F,-1)))
B2	B2	=INDEX(F:F,MATCH(H2,F:F,-1))-H2
C2	C2	=COUNTIFS(F:F,">="&H2,F:F,"<="&EOMONTH(H2,0))
D2	D2	=COUNTIFS(F:F,">="&H2,F:F,"<="&DATEVALUE("31 dec"&YEAR(H2)))
F3:F33	F3	=F2-14

 

[etaf]

Just change
">="&TODAY()
to
">"&TODAY()
Now it will NOT include the payday and so will show zero

Payday-ETAF using H2.xlsx
	A	B	C	D	E	F	G	H
1	Business Days	Calendar Days	Pay Days in Month	Pay Days to year end		LOOKUP		DATE
2	1	0	0	2		12/24/21		11/27/20
3						12/10/21
4						11/26/21
5						11/12/21
6						10/29/21
7						10/15/21
8						10/1/21
9						9/17/21
10						9/3/21
11						8/20/21
12						8/6/21
13						7/23/21
14						7/9/21
15						6/25/21
16						6/11/21
17						5/28/21
18						5/14/21
19						4/30/21
20						4/16/21
21						4/2/21
22						3/19/21
23						3/5/21
24						2/19/21
25						2/5/21
26						1/22/21
27						1/8/21
28						12/25/20
29						12/11/20
30						11/27/20
31						11/13/20
32						10/30/20
33						10/16/20
Sheet1

Cell Formulas
Range	Formula
A2	A2	=NETWORKDAYS(H2,INDEX(F:F,MATCH(H2,F:F,-1)))
B2	B2	=INDEX(F:F,MATCH(H2,F:F,-1))-H2
C2	C2	=COUNTIFS(F:F,">"&H2,F:F,"<="&EOMONTH(H2,0))
D2	D2	=COUNTIFS(F:F,">"&H2,F:F,"<="&DATEVALUE("31 dec"&YEAR(H2)))
F3:F33	F3	=F2-14

 

[banneduser123]

seems you have answered all the questions now.
Note that using
">="&TODAY() will include Today and so on pay day will count that day , as it is greater than or equal to
so on the 27th Nov 2020 , which is a payday , it will still show 1 payday in month which includes 27th Nov
Just change
">="&TODAY()
to
">"&TODAY()
Now it will NOT include the payday and so will show zero

Payday-ETAF using H2.xlsx
	A	B	C	D	E	F	G	H
1	Business Days	Calendar Days	Pay Days in Month	Pay Days to year end		LOOKUP		DATE
2	1	0	1	3		12/24/21		11/27/20
3						12/10/21
4						11/26/21
5						11/12/21
6						10/29/21
7						10/15/21
8						10/1/21
9						9/17/21
10						9/3/21
11						8/20/21
12						8/6/21
13						7/23/21
14						7/9/21
15						6/25/21
16						6/11/21
17						5/28/21
18						5/14/21
19						4/30/21
20						4/16/21
21						4/2/21
22						3/19/21
23						3/5/21
24						2/19/21
25						2/5/21
26						1/22/21
27						1/8/21
28						12/25/20
29						12/11/20
30						11/27/20
31						11/13/20
32						10/30/20
33						10/16/20
Sheet1

Cell Formulas
Range	Formula
A2	A2	=NETWORKDAYS(H2,INDEX(F:F,MATCH(H2,F:F,-1)))
B2	B2	=INDEX(F:F,MATCH(H2,F:F,-1))-H2
C2	C2	=COUNTIFS(F:F,">="&H2,F:F,"<="&EOMONTH(H2,0))
D2	D2	=COUNTIFS(F:F,">="&H2,F:F,"<="&DATEVALUE("31 dec"&YEAR(H2)))
F3:F33	F3	=F2-14

thanks again for all the help.

how can i incorporate holidays into these formulas?
I'm trying to toy around with the WORKDAY function, but not getting it right.

 

[etaf]

networkday()
NETWORKDAYS(start_date, end_date, [holidays]) or WORKDAY(start_date, days, [holidays])
The argument for [holidays] , would be a list of days that are not included in the count of days
you just specify the range , for example
say $H$2:$H$100
add your holiday dates or dates you dont want counted into the list

 

[banneduser123]

networkday()
NETWORKDAYS(start_date, end_date, [holidays]) or WORKDAY(start_date, days, [holidays])
The argument for [holidays] , would be a list of days that are not included in the count of days
you just specify the range , for example
say $H$2:$H$100
add your holiday dates or dates you dont want counted into the list

ah nice. didn't realize NETWORKDAYS also had the [holidays] argument.