rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
181
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...
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,156
Office Version
  1. 365
Platform
  1. MacOS
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 ?

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
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
181
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 ?

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
Joined
Oct 24, 2012
Messages
4,156
Office Version
  1. 365
Platform
  1. MacOS
I think this should work
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,156
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
181
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
Joined
Oct 24, 2012
Messages
4,156
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

usually always a 31st Dec in all years
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,156
Office Version
  1. 365
Platform
  1. MacOS
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:
Solution

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
181
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,784
Messages
5,574,243
Members
412,579
Latest member
ffbaker632
Top