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]

i'm UK based and so the date format is based on the computer preferences for region i think
what country are you

 

[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

i moved things around and the paycheck cells are working perfectly..

having trouble with the calendar days (produces 1) and business days (produces 2)...any ideas?

 

[banneduser123]

i'm UK based and so the date format is based on the computer preferences for region i think
what country are you

USA. ok, i thought i would have to adjust it under Options...good to know...i've been toying around with custom date and number formats lately

 

[etaf]

not without seeing the info you have in those cells ?
or a link to the spreadsheet on a share like dropbox or onedrive


Its NOT an excel setting, its a PC setting

You can change the way the date is displayed using format
MM/DD/YYYY
or various other formats

the default will be in the PC region settings on windows , system preferences, in OSX apple

 

[banneduser123]

not without seeing the info you have in those cells ?
or a link to the spreadsheet on a share like dropbox or onedrive


Its NOT an excel setting, its a PC setting

oops. forgot to include:


BDs: =NETWORKDAYS(TODAY(),INDEX(Data!A:A,MATCH(TODAY(),Data!A:A,-1)))
CDs: =INDEX(Data!A:A,MATCH(TODAY(),Data!A:A,-1))-TODAY()


may take a bit on the onedrive/dropdox. i deleted my accounts i think


**the problem is with the sheet called Data....i wasn't starting starting with 12/24/21 as we did previously...let me see what's going on..

 

[etaf]

So in column A , you have a list of dates for paydays for the year , are they the same dates as i have in F:F and in the reverse order ?
thats what the -1 needs to work in the index/match lookup , to find the nearest higher value from today

BDs: =NETWORKDAYS(TODAY(),INDEX(Data!A:A,MATCH(TODAY(),Data!A:A,-1)))
CDs: =INDEX(Data!A:A,MATCH(TODAY(),Data!A:A,-1))-TODAY()

A2	A2	=NETWORKDAYS(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2	B2	=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()

 

[banneduser123]

So in column A , you have a list of dates for paydays for the year , are they the same dates as i have in F:F and in the reverse order ?
thats what the -1 needs to work in the index/match lookup , to find the nearest higher value from today

A2	A2	=NETWORKDAYS(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2	B2	=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()

i think we're good here..
i think i'm going to eventually want to see how to create a formula that would produce the same results, if i were to enter a random date in cell "F2".

would be ideal to have the luxury of inputting a random date in cell F2, and in the end it would still deliver the same results. but have to think about that another day.

this is perfect. really appreciate the help

 

[etaf]

not sure what F2 refers today

you could replace today() with a cell reference where you can put a date , and then the formula would use that date as if it was todays date

lets say you used H2 as a date to pretend its a different day - to see what results you get
then
replace today() with H2

Dropbox - File Deleted - Simplify your life

www.dropbox.com

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	5	6	1	3		12/24/21		11/21/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]

not sure what F2 refers today

you could replace today() with a cell reference where you can put a date , and then the formula would use that date as if it was todays date

lets say you used H2 as a date to pretend its a different day - to see what results you get
then
replace today() with H2

Dropbox - File Deleted - Simplify your life

www.dropbox.com

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	5	6	1	3		12/24/21		11/21/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

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

 

[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

you're the best. this was all really helpful