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

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### etaf

##### Well-known Member

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 ?

Cell Formulas
RangeFormula
A2A2=NETWORKDAYS(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2B2=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()
F3:F29F3=F2-14

#### banneduser123

##### Banned - Rules violations

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 ?

Cell Formulas
RangeFormula
A2A2=NETWORKDAYS(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2B2=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()
F3:F29F3=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

##### Well-known Member
I think this should work

#### etaf

##### Well-known Member

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

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

##### Well-known Member

usually always a 31st Dec in all years

#### banneduser123

##### Banned - Rules violations
usually always a 31st Dec in all years
hahah

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

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

#### etaf

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

Last edited:

#### banneduser123

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

Replies
8
Views
187
Replies
4
Views
88
Replies
1
Views
112
Replies
11
Views
306
Replies
5
Views
136

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,822
Messages
5,772,469
Members
425,760
Latest member
zj042060

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