Networkdays

[BeeLife]

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

 

[BeeLife]

I think it should be ....
startdate = today & B1 blank = networkdays between A1 and B1 (should be 1 (or 0 if weekend (unlikely)))
startdate = today & B1 with date = networkdays between A1 and B1
startdate after today & B1 blank = 1
startdate after today & B1 with date =networkdays between A1 and B1 (this already works in the formula)

they’ll always be a start date.

image attached for context

 

[BeeLife]

Image attached this time

 

[MARK858]

startdate = today & B1 blank = networkdays between A1 and B1 (should be 1 (or 0 if weekend (unlikely)))

How can the bit in red be true if B1 is blank

 

[BeeLife]

Sorry ... it assume B1 is “today” IF today is the startdate.

Most of the formula works as required. It’s just where there is a startdate in the future and blank end date, it counts backwards.

 

[MARK858]

Sorry ... it assume B1 is “today” IF today is the startdate.

Most of the formula works as required. It’s just where there is a startdate in the future and blank end date, it counts backwards.

Sorry to be a pain but should that return 1?

 

[BeeLife]

Yes - by default and it should calculate 1 day.

today + today = 1 day ( assuming today is a networkday)

No need to apologise. I’m taking up your time and I’m extremely grateful for your assistance

 

[MARK858]

See where we are with the below...

Book1
	A	B	C
1	09/09/2020		1
Sheet2

Cell Formulas
Range	Formula
C1	C1	=IF(AND(A1>=TODAY(),B1=""),1,IF(B1<>"",NETWORKDAYS(A1,B1),NETWORKDAYS(A1,TODAY())))

 

[BeeLife]

Perfect! I can’t thank you enough.

 

[MARK858]

You're welcome