Date and Networkdays

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
122
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,978
Office Version
  1. 365
Platform
  1. Windows
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.
 

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
122
Office Version
  1. 365
Platform
  1. Windows
Thank you for your guidance.it is actually the closest working day on or after the end of month date.
 

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
122
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In B2 one enters the date. Based on the value in B4 column
Loan Sheet.xlsx
V
11
Sheet1
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,978
Office Version
  1. 365
Platform
  1. Windows
You need to select the range that you want to post before running the XL2BB addin.
 

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
122
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
122
Office Version
  1. 365
Platform
  1. Windows
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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,978
Office Version
  1. 365
Platform
  1. Windows
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))
 

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
122
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

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