# Formulas referencing Pay Dates in year

#### banneduser123

##### Banned - Rules violations
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

##### Banned - Rules violations
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
ABCDE
1Input Date12/25/2020Average Paycheck\$500
2
3Current 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
FGHIJ
42Input DateAverage Paycheck
431/15/2021500
44PCs left NovPCs left 2020
4513
46Expected Income25\$ 12,500.00
2020
Cell Formulas
RangeFormula
F45F45=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&EOMONTH(TODAY(),0))
G45G45=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&DATEVALUE("31 dec"&YEAR(TODAY())))
G46G46=COUNTIFS(Data!A:A,">="&H43,Data!A:A,"<="&DATEVALUE("31 dec"&YEAR(H43)))
H46H46=I43*G46

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### banneduser123

##### Banned - Rules violations
**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
FGHIJ
42Input DateAverage Paycheck
431/15/2021500
44PCs left NovPCs left 2020
4513
46Expected Income25\$ 12,500.00
2020
Cell Formulas
RangeFormula
F45F45=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&EOMONTH(TODAY(),0))
G45G45=COUNTIFS(Data!A:A,">="&TODAY(),Data!A:A,"<="&DATEVALUE("31 dec"&YEAR(TODAY())))
G46G46=COUNTIFS(Data!A:A,">="&H43,Data!A:A,"<="&DATEVALUE("31 dec"&YEAR(H43)))
H46H46=I43*G46

i think i got it.

going to try using this:

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

#### banneduser123

##### Banned - Rules violations
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

##### Well-known Member
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
ABCDEFGH
1Business DaysCalendar DaysPay Days in Month Pay Days to year endLOOKUPDATE
2101312/24/2111/27/20
312/10/21
411/26/21
511/12/21
610/29/21
710/15/21
810/1/21
99/17/21
109/3/21
118/20/21
128/6/21
137/23/21
147/9/21
156/25/21
166/11/21
175/28/21
185/14/21
194/30/21
204/16/21
214/2/21
223/19/21
233/5/21
242/19/21
252/5/21
261/22/21
271/8/21
2812/25/20
2912/11/20
3011/27/20
3111/13/20
3210/30/20
3310/16/20
Sheet1
Cell Formulas
RangeFormula
A2A2=NETWORKDAYS(H2,INDEX(F:F,MATCH(H2,F:F,-1)))
B2B2=INDEX(F:F,MATCH(H2,F:F,-1))-H2
C2C2=COUNTIFS(F:F,">="&H2,F:F,"<="&EOMONTH(H2,0))
D2D2=COUNTIFS(F:F,">="&H2,F:F,"<="&DATEVALUE("31 dec"&YEAR(H2)))
F3:F33F3=F2-14

#### etaf

##### Well-known Member

Just change
">="&TODAY()
to
">"&TODAY()
Now it will NOT include the payday and so will show zero
Payday-ETAF using H2.xlsx
ABCDEFGH
1Business DaysCalendar DaysPay Days in Month Pay Days to year endLOOKUPDATE
2100212/24/2111/27/20
312/10/21
411/26/21
511/12/21
610/29/21
710/15/21
810/1/21
99/17/21
109/3/21
118/20/21
128/6/21
137/23/21
147/9/21
156/25/21
166/11/21
175/28/21
185/14/21
194/30/21
204/16/21
214/2/21
223/19/21
233/5/21
242/19/21
252/5/21
261/22/21
271/8/21
2812/25/20
2912/11/20
3011/27/20
3111/13/20
3210/30/20
3310/16/20
Sheet1
Cell Formulas
RangeFormula
A2A2=NETWORKDAYS(H2,INDEX(F:F,MATCH(H2,F:F,-1)))
B2B2=INDEX(F:F,MATCH(H2,F:F,-1))-H2
C2C2=COUNTIFS(F:F,">"&H2,F:F,"<="&EOMONTH(H2,0))
D2D2=COUNTIFS(F:F,">"&H2,F:F,"<="&DATEVALUE("31 dec"&YEAR(H2)))
F3:F33F3=F2-14

#### banneduser123

##### Banned - Rules violations
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
ABCDEFGH
1Business DaysCalendar DaysPay Days in Month Pay Days to year endLOOKUPDATE
2101312/24/2111/27/20
312/10/21
411/26/21
511/12/21
610/29/21
710/15/21
810/1/21
99/17/21
109/3/21
118/20/21
128/6/21
137/23/21
147/9/21
156/25/21
166/11/21
175/28/21
185/14/21
194/30/21
204/16/21
214/2/21
223/19/21
233/5/21
242/19/21
252/5/21
261/22/21
271/8/21
2812/25/20
2912/11/20
3011/27/20
3111/13/20
3210/30/20
3310/16/20
Sheet1
Cell Formulas
RangeFormula
A2A2=NETWORKDAYS(H2,INDEX(F:F,MATCH(H2,F:F,-1)))
B2B2=INDEX(F:F,MATCH(H2,F:F,-1))-H2
C2C2=COUNTIFS(F:F,">="&H2,F:F,"<="&EOMONTH(H2,0))
D2D2=COUNTIFS(F:F,">="&H2,F:F,"<="&DATEVALUE("31 dec"&YEAR(H2)))
F3:F33F3=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

##### Well-known Member
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

##### Banned - Rules violations
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.

Replies
8
Views
284
Replies
6
Views
85
Replies
14
Views
211
Replies
2
Views
403
Replies
0
Views
176

1,127,683
Messages
5,626,275
Members
416,170
Latest member
Urraco

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back