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

 

[MARK858]

Maybe...

Book1
	A	B	C
1	01/09/2020	11/09/2020	9
2	01/09/2020		6
Sheet4

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

 

[BeeLife]

Maybe...

Book1
	A	B	C
1	01/09/2020	11/09/2020	9
2	01/09/2020		6
Sheet4

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

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

 

[MARK858]

Happy it helped and welcome to the forum.

 

[BeeLife]

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]

Maybe either of the formulas below

Book1
	A	B	C
1	09/09/2020		2
2	10/09/2020		3
Sheet1

Cell Formulas
Range	Formula
C1	C1	=IF(AND(A1>TODAY(),B1=""),ABS(NETWORKDAYS(A1,TODAY())),IF(B1<>"",NETWORKDAYS(A1,B1),NETWORKDAYS(A1,TODAY())))
C2	C2	=IF(AND(A2>TODAY(),B2=""),NETWORKDAYS(A2,TODAY())*-1,IF(B2<>"",NETWORKDAYS(A2,B2),NETWORKDAYS(A2,TODAY())))

 

[BeeLife]

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.

 

[MARK858]

it the cell should return a value of 1 or 0

Not clear what the conditions are to decide if it should be a 1 or 0.

 

[BeeLife]

I’ve confused matters by introducing a 0. It should return “1” if the start date is in the future.

Thanks

 

[MARK858]

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 = ????