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...

 

[etaf]

networkdays() should give business days

if the table of dates can be added backwards as shown, put the last date needed and then subtract 14 - BUT i assume if that days is a saturday or sunday then payday is the friday - is that so ?

Book1
	A	B	C	D	E	F
1	Business Days	Calendar Days				LOOKUP
2	8	11				1/13/21
3						12/30/20
4						12/16/20
5						12/2/20
6						11/18/20
7						11/4/20
8						10/21/20
9						10/7/20
10						9/23/20
11						9/9/20
12						8/26/20
13						8/12/20
14						7/29/20
15						7/15/20
16						7/1/20
17						6/17/20
18						6/3/20
19						5/20/20
20						5/6/20
21						4/22/20
22						4/8/20
23						3/25/20
24						3/11/20
25						2/26/20
26						2/12/20
27						1/29/20
28						1/15/20
29						1/1/20
Sheet1

Cell Formulas
Range	Formula
A2	A2	=NETWORKDAYS(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2	B2	=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()
F3:F29	F3	=F2-14

 

[banneduser123]

networkdays() should give business days

if the table of dates can be added backwards as shown, put the last date needed and then subtract 14 - BUT i assume if that days is a saturday or sunday then payday is the friday - is that so ?

Book1
	A	B	C	D	E	F
1	Business Days	Calendar Days				LOOKUP
2	8	11				1/13/21
3						12/30/20
4						12/16/20
5						12/2/20
6						11/18/20
7						11/4/20
8						10/21/20
9						10/7/20
10						9/23/20
11						9/9/20
12						8/26/20
13						8/12/20
14						7/29/20
15						7/15/20
16						7/1/20
17						6/17/20
18						6/3/20
19						5/20/20
20						5/6/20
21						4/22/20
22						4/8/20
23						3/25/20
24						3/11/20
25						2/26/20
26						2/12/20
27						1/29/20
28						1/15/20
29						1/1/20
Sheet1

Cell Formulas
Range	Formula
A2	A2	=NETWORKDAYS(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2	B2	=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()
F3:F29	F3	=F2-14

thanks a lot etaf.

correct on payday being fridays. pay schedule is every 2 weeks.

just tried throwing it into a workbook, but i'm getting a 0 in the Business days cell and a 1 in the Calendar Days cell. any idea why?

also, if it helps, let try to condense my query:

i'm trying to accomplish this:

as i'm looking at the calendar myself, i know the next pay day is 11/27/20.

in my worksheet i'd like to have 3 cells that would produce the below results:

how many Business Days until i get my next paycheck? (5)
how many more paychecks do i get for the rest of November? (1)
how many more paychecks for the rest of the year? (3)


again, really appreciate the help

 

[etaf]

I think this should work

 

[etaf]

Payday-ETAF.xlsx
	A	B	C	D	E	F
1	Business Days	Calendar Days	Pay Days in Month	Pay Days to year end		LOOKUP
2	5	6	1	3		12/24/21
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(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2	B2	=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()
C2	C2	=COUNTIFS(F:F,">="&TODAY(),F:F,"<="&EOMONTH(TODAY(),0))
D2	D2	=COUNTIFS(F:F,">="&TODAY(),F:F,"<="&DATEVALUE("31/12/"&YEAR(TODAY())))
F3:F33	F3	=F2-14

 

[banneduser123]

Payday-ETAF.xlsx
	A	B	C	D	E	F
1	Business Days	Calendar Days	Pay Days in Month	Pay Days to year end		LOOKUP
2	5	6	1	3		12/24/21
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(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2	B2	=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()
C2	C2	=COUNTIFS(F:F,">="&TODAY(),F:F,"<="&EOMONTH(TODAY(),0))
D2	D2	=COUNTIFS(F:F,">="&TODAY(),F:F,"<="&DATEVALUE("31/12/"&YEAR(TODAY())))
F3:F33	F3	=F2-14

wow. well done etat.

i'm having trouble on cell D3 though.

for me, i'm getting a 0, having trouble seeing why.
cell format is set to General.
any ideas?


**ohh, i think it's because there's no "12/31" in 2020?

 

[etaf]

usually always a 31st Dec in all years

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[banneduser123]

usually always a 31st Dec in all years

Dropbox - File Deleted - Simplify your life

www.dropbox.com

hahah

right, but how do you make this part a variable?

DATEVALUE("31/12/"&YEAR(TODAY())))

 

[etaf]

this is to get the end of the year, are you american format MM/DD ?
maybe
DATEVALUE("12/31/"&YEAR(TODAY())))
or
=DATEVALUE("31 dec"&YEAR(TODAY()))

it will show the number of paydays to the end of the current year based on TODAY() so on 1st jan 2021 - it will show all the paydays till end of 21

 

[banneduser123]

this is to get the end of the year, are you american format MM/DD ?
maybe
DATEVALUE("12/31/"&YEAR(TODAY())))
or
=DATEVALUE("31 dec"&YEAR(TODAY()))

it will show the number of paydays to the end of the current year based on TODAY() so on 1st jan 2021 - it will show all the paydays till end of 21

wow..beautiful. thanks so much.

what format do you have it in and where would i change that, if i wanted to?

i may come back to you as i see what i can derive using this...thanks again