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

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
i'm UK based and so the date format is based on the computer preferences for region i think
what country are you
 

Some videos you may like

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

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

ADVERTISEMENT

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

A2A2=NETWORKDAYS(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2B2=INDEX(F:F,MATCH(TODAY(),F:F,-1))-TODAY()
 

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181

ADVERTISEMENT

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



A2A2=NETWORKDAYS(TODAY(),INDEX(F:F,MATCH(TODAY(),F:F,-1)))
B2B2=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
Joined
Oct 24, 2012
Messages
4,587
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Mar 29, 2006
Messages
181
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
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
you're the best. this was all really helpful
 

Watch MrExcel Video

Forum statistics

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