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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
Solution
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
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

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