banneduser123

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

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
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
 

Some videos you may like

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
Joined
Mar 29, 2006
Messages
181
**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))
 

etaf

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

ADVERTISEMENT

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

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top