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

#### etaf

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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### banneduser123

##### Banned - Rules violations
Payday-ETAF.xlsx
ABCDEF
1Business DaysCalendar DaysPay Days in Month Pay Days to year endLOOKUP
2561312/24/21
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(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2B2=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()
C2C2=COUNTIFS(F:F,">="&TODAY(),F:F,"<="&EOMONTH(TODAY(),0))
D2D2=COUNTIFS(F:F,">="&TODAY(),F:F,"<="&DATEVALUE("31/12/"&YEAR(TODAY())))
F3:F33F3=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

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

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

Last edited:

#### banneduser123

##### Banned - Rules violations

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

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

##### Banned - Rules violations

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

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

Payday-ETAF using H2.xlsx
ABCDEFGH
1Business DaysCalendar DaysPay Days in Month Pay Days to year endLOOKUPDATE
2561312/24/2111/21/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
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

Payday-ETAF using H2.xlsx
ABCDEFGH
1Business DaysCalendar DaysPay Days in Month Pay Days to year endLOOKUPDATE
2561312/24/2111/21/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
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

#### 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
you're the best. this was all really helpful

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

1,127,687
Messages
5,626,300
Members
416,172
Latest member
lordposh

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

### Which adblocker are you using?

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

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