Networkdays

BeeLife

New Member
Joined
Sep 7, 2020
Messages
10
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
  2. Mobile
Hi there, apologies in advance as I assume my query has been answered at some point in the past, but I haven’t come across a simple solution.

I’m trying to calculate the number of workdays / networkdays (total days excluding weekends) between a start date and end date, but if the end date cell is blank, it assumes ‘today’ is the end date, until an end date is entered.

Hope that makes sense.

Thank you
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,838
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe...

Book1
ABC
101/09/202011/09/20209
201/09/20206
Sheet4
Cell Formulas
RangeFormula
C1:C2C1=IF(B1<>"",NETWORKDAYS(A1,B1),NETWORKDAYS(A1,TODAY()))
 

BeeLife

New Member
Joined
Sep 7, 2020
Messages
10
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe...

Book1
ABC
101/09/202011/09/20209
201/09/20206
Sheet4
Cell Formulas
RangeFormula
C1:C2C1=IF(B1<>"",NETWORKDAYS(A1,B1),NETWORKDAYS(A1,TODAY()))

Great! Thank you so much .... it works perfectly
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,838
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Happy it helped and welcome to the forum.
 

BeeLife

New Member
Joined
Sep 7, 2020
Messages
10
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Thanks again .... it’s probably not possible but can anything be added to the formula to avoid it displaying minus days (-1)? So for example if the start date is in the future but there is no end date, the value returned is 1.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,838
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe either of the formulas below
Book1
ABC
109/09/20202
210/09/20203
Sheet1
Cell Formulas
RangeFormula
C1C1=IF(AND(A1>TODAY(),B1=""),ABS(NETWORKDAYS(A1,TODAY())),IF(B1<>"",NETWORKDAYS(A1,B1),NETWORKDAYS(A1,TODAY())))
C2C2=IF(AND(A2>TODAY(),B2=""),NETWORKDAYS(A2,TODAY())*-1,IF(B2<>"",NETWORKDAYS(A2,B2),NETWORKDAYS(A2,TODAY())))
 

BeeLife

New Member
Joined
Sep 7, 2020
Messages
10
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Sorry ... I didn’t explain property. I meant because it’s a future date, it the cell should return a value of 1 or 0 (exactly 1 or 0) until the start date passes or when an end date is entered in the blank cell.

I really appreciate your assistance, thank you so much.
 

BeeLife

New Member
Joined
Sep 7, 2020
Messages
10
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
  2. Mobile
I’ve confused matters by introducing a 0. It should return “1” if the start date is in the future.

Thanks
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,838
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Ok, we are a bit closer, can you check the one's where I have filled in the required result and fill in where I have ???? please

startdate before today & B1 blank = networkdays with Today
startdate before today & B1 with date = networkdays with b1 date
startdate = today & B1 blank = ????
startdate = today & B1 with date = ????
startdate after today & B1 blank = 1
startdate after today & B1 with date = ????
 

Watch MrExcel Video

Forum statistics

Threads
1,122,632
Messages
5,597,287
Members
414,134
Latest member
Tiyas44

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