Date and Networkdays

S

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
150
Office Version
  1. 365
Platform
  1. Windows
I am currently using this formula to provide end of month dates which works perfectly but what I want to do in addition is to make the output to be networkdays only but I cannot workout where I need to add this within the formula to make that happen.
=IF(ROWS(E$2:E2)>($B$4*12);"";EOMONTH(TRIM($B$2);ROWS(E$2:E2)-1))

In addition to this in cell B2 is it possible to have a date format that will let an end user type in a date in dd/mm/yyyy format and get a long date output. when I use the date function it wants me to input in this order yy/mm/dd
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Do you want 'networkdays' or 'workday'?

Networkdays is the number of working days between 2 dates.
Workday is the working day closest to the specified date. (If this is what you want, then should it be the closest day before or after?)

The DATE function only allows dates in yy/mm/dd format. If dd/mm/yyyy is consistent with your regional settings then you can enter dates into a cell in that format without using the date function, which can then be formatted as required.
 
Upvote 0
Thank you for your guidance.it is actually the closest working day on or after the end of month date.
 
Upvote 0
In B2 one enters the date. Based on the value in B4 column
Loan Sheet.xlsx
V
11
Sheet1
 
Upvote 0
You need to select the range that you want to post before running the XL2BB addin.
 
Upvote 0
That was sent inadvertently. What I was saying that in column E subject to the value in B4 will be shown either 12,24 0r 36 dates but I would like those dates to be end of month working days either the last working day of the month or if that falls on a non working day then the first of the next month.
 
Upvote 0
Loan Sheet.xlsx
ABCDEFGHIJK
1Month NoPayment DateMonthly Interest PaymentMonthly Capital RepaymentAccumulated Interest Earned% Accumulated Interest EarnedCapital RepaidOutstanding Capital
2Loan Date Thursday, 15 October 20201Saturday, 31 October 2020499,730499,730,50%0100000,00
3Loan Amount1000002Monday, 30 November 2020936,9901436,711,44%0100000,00
4Loan Term (Years)13Thursday, 31 December 2020968,2210000,002404,932,40%10000,0090000,00
5% Monthly Interest Rate0,950%4Sunday, 31 January 2021968,2210000,003373,153,37%20000,0080000,00
6% Annual Interest Rate0,1145Sunday, 28 February 2021874,5210000,004247,674,25%30000,0070000,00
7Monthly Interest Payment950,006Wednesday, 31 March 2021968,2210000,005215,895,22%40000,0060000,00
8Monthly Capital Payment*10000,007Friday, 30 April 2021936,9910000,006152,886,15%50000,0050000,00
9Total Interest114008Monday, 31 May 2021968,2210000,007121,107,12%60000,0040000,00
10Total Capital1000009Wednesday, 30 June 2021936,9910000,008058,088,06%70000,0030000,00
11Total Return11140010Saturday, 31 July 2021968,2210000,009026,309,03%80000,0020000,00
12No of Interest Payments1211Tuesday, 31 August 2021968,2210000,009994,529,99%90000,0010000,00
13No Capital Payments1012Thursday, 30 September 2021936,9910000,0010931,5110,93%100000,000,00
1413       
1514       
1615       
1716       
1817       
1918       
2019       
2120       
2221       
2322       
2423       
2524       
2625       
2726       
2827       
2928       
3029       
3130       
3231       
3332       
3433       
3534       
3635       
3736       
Sheet1
Cell Formulas
RangeFormula
E2:E37E2=IF(ROWS(E$2:E2)>($B$4*12),"",EOMONTH(TRIM($B$2),ROWS(E$2:E2)-1))
F2F2=(B3*(B5*12)/365)*(E2-B2)
F3:F37F3=IFERROR(($B$3*($B$5*12)/365)*(E3-E2),"")
H2H2=IF(E2>0,F2)
I2I2=H2/$B$3
J2:J3J2=G2
K2K2=B3-G2
H3:H37H3=IFERROR(IF(E3>0,F3+H2),"")
I3:I37I3=IFERROR(IF(E3>0,H3/$B$3),"")
K3K3=K2-G3
G4:G37G4=IFERROR(IF(E4="","",$B$3/$B$13),"")
J4:J37J4=IFERROR(IF(E4>1,G4+J3),"")
K4:K37K4=IFERROR(IF(E4>0,K3-G4),"")
B5B5=INDEX($O$3:$O$5,MATCH($B$3,$N$3:$N$5,1))+INDEX($Q$3:$Q$5,MATCH($B$4,$P$3:$P$5,1))
B6B6=B5*12
B7B7=B3*B5
B8B8=B3/INDEX($R$3:$R$5,MATCH($B$4,$P$3:$P$5))
B9B9=B7*(B4*12)
B10B10=B8*INDEX($R$3:$R$5,MATCH($B$4,$P$3:$P$5))
B11B11=B9+B10
B12B12=B4*12
B13B13=INDEX(Sheet2!$C$1:$C$3,MATCH(Sheet1!B4,Sheet2!$A$1:$A$3))
Cells with Data Validation
CellAllowCriteria
B4:C4List=Sheet2!$A$1:$A$3
C13:C37List=Sheet2!$A$1:$A$3
 
Upvote 0
You just need to replace the EOMONTH part of your formula with the one that @Dave Patton suggested.

=IF(ROWS(E$2:E2)>($B$4*12),"",WORKDAY(EOMONTH(B2,0)-1,1))
 
Upvote 0
I did try that formula but it returned the resuults as per below.
Loan Sheet.xlsx
ABCDEFGHIJK
1Month NoPayment DateMonthly Interest PaymentMonthly Capital RepaymentAccumulated Interest Earned% Accumulated Interest EarnedCapital RepaidOutstanding Capital
2Loan Date Thursday, 15 October 20201Monday, 02 November 2020562,190562,190,56%0100000,00
3Loan Amount1000002Monday, 01 November 21731745324,3801745886,581745,89%0100000,00
4Loan Term (Years)13Tuesday, 31 January 1900-3122881,6410000,00-1376995,07-1377,00%10000,0090000,00
5% Monthly Interest Rate0,950%4Tuesday, 31 January 19000,0010000,00-1376995,07-1377,00%20000,0080000,00
6% Annual Interest Rate0,1145Tuesday, 31 January 19000,0010000,00-1376995,07-1377,00%30000,0070000,00
7Monthly Interest Payment950,006Monday, 01 September 190229483,8410000,00-1347511,23-1347,51%40000,0060000,00
8Monthly Capital Payment*10000,007Tuesday, 31 May 1927282282,7410000,00-1065228,49-1065,23%50000,0050000,00
9Total Interest114008Tuesday, 31 March 193143726,0310000,00-1021502,47-1021,50%60000,0040000,00
10Total Capital1000009Monday, 01 November 21732767389,0410000,001745886,581745,89%70000,0030000,00
11Total Return11140010Monday, 31 December 2204355492,6010000,002101379,182101,38%80000,0020000,00
12No of Interest Payments1211Tuesday, 31 January 1900-3478374,2510000,00-1376995,07-1377,00%90000,0010000,00
13No Capital Payments1012Thursday, 30 September 20211387926,5810000,0010931,5110,93%100000,000,00
1413       
1514       
1615       
1716       
1817       
1918       
2019       
2120       
2221       
2322       
2423       
2524       
2625       
2726       
2827       
2928       
3029       
3130       
3231       
3332       
3433       
3534       
3635       
3736       
Sheet1
Cell Formulas
RangeFormula
E2:E12E2=IF(ROWS(E$2:E2)>($B$4*12),"",WORKDAY(EOMONTH(B2,0)-1,1))
F2F2=(B3*(B5*12)/365)*(E2-B2)
F3:F37F3=IFERROR(($B$3*($B$5*12)/365)*(E3-E2),"")
H2H2=IF(E2>0,F2)
I2I2=H2/$B$3
J2:J3J2=G2
K2K2=B3-G2
H3:H37H3=IFERROR(IF(E3>0,F3+H2),"")
I3:I37I3=IFERROR(IF(E3>0,H3/$B$3),"")
K3K3=K2-G3
G4:G37G4=IFERROR(IF(E4="","",$B$3/$B$13),"")
J4:J37J4=IFERROR(IF(E4>1,G4+J3),"")
K4:K37K4=IFERROR(IF(E4>0,K3-G4),"")
E13:E37E13=IF(ROWS(E$2:E13)>($B$4*12),"",EOMONTH(TRIM($B$2),ROWS(E$2:E13)-1))
B5B5=INDEX($O$3:$O$5,MATCH($B$3,$N$3:$N$5,1))+INDEX($Q$3:$Q$5,MATCH($B$4,$P$3:$P$5,1))
B6B6=B5*12
B7B7=B3*B5
B8B8=B3/INDEX($R$3:$R$5,MATCH($B$4,$P$3:$P$5))
B9B9=B7*(B4*12)
B10B10=B8*INDEX($R$3:$R$5,MATCH($B$4,$P$3:$P$5))
B11B11=B9+B10
B12B12=B4*12
B13B13=INDEX(Sheet2!$C$1:$C$3,MATCH(Sheet1!B4,Sheet2!$A$1:$A$3))
Cells with Data Validation
CellAllowCriteria
B4:C4List=Sheet2!$A$1:$A$3
C13:C37List=Sheet2!$A$1:$A$3
 
Upvote 0
You must log in or register to reply here.

Similar threads

AndyTampa
  • Solved
InputBox to ask for date and only accept standard date formats
2 3
Replies
27
Views
1K
AndyTampa
AndyTampa
A
  • Solved
NETWORKDAYS vs COUNTIFS
Replies
2
Views
135
amberd
A
G
  • Solved
History of Dates to be traced in Excel (2010) in DD/MM/YYYY format
2
Replies
11
Views
344
MARK858
MARK858
T
  • Solved
Problem with date format when reading from workbook via SQL / ADODB
Replies
7
Views
333
Thebatfink
T
S
  • Solved
Use change event to change the format of a date
Replies
2
Views
139
still learning
S

Forum statistics

Threads
1,214,376
Messages
6,119,179
Members
448,871
Latest member
hengshankouniuniu

Share this page

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