Calculate days between three dates

[SaraWitch]

Hello peeps,

I want to calculate the days remaining from an expected end date and today but if a completion date is entered, then calculate number of days between this and received date.

I can get the formula to calculate the first bit but not the second:

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G
1	Address	Notice received date	Expected end date	Pre-work			Days to complete
2				Required	Date completed	Days to complete	should be
3	Gnome Cave	08/07/2024	04/08/2024	Yes	10/07/2024	16	2
Sheet3

Cell Formulas
Range	Formula
C3	C3	=B3+27
F3	F3	=IF(A3="","",IF(D3="No","",IF(D3="","",IF(D3="Yes",C3-TODAY(),IF(E3>0,B3-E3)))))

Cells with Data Validation
Cell	Allow	Criteria
D3	List	Yes, No

Hope this makes sense, and any help would be appreciated!

 

[Snakehips]

@SaraWitch Does this help?

AutoTraining2.xlsm
	A	B	C	D	E	F
1	Address	Notice received date	Expected end date	Pre-work
2				Required	Date completed	Days to complete
3	Gnome Cave	45481	45508	Yes	45483	2
Sheet5

Cell Formulas
Range	Formula
C3	C3	=B3+27
F3	F3	=IF(OR(A3="",D3<>"Yes"),"",IF(E3>1,E3-B3,C3-TODAY()))

 

[SaraWitch]

You experts make it seem so easy! Of course, this makes perfect sense - thank you so much, @Snakehips!

 

[SaraWitch]

And if I wanted to calculate only working days...?

 

[Joe4]

And if I wanted to calculate only working days...?

Use the NETWORKDAYS function:

=IF(OR(A3="",D3<>"Yes"),"",IF(E3>1,NETWORKDAYS(B3,E3),NETWORKDAYS(TODAY(),C3)))

See here for more details on that function: NETWORKDAYS function - Microsoft Support

 

[SaraWitch]

Brilliant! Thank you, Joe!

I've also added to it to exclude bank holidays, which seems to work:

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G	H	I	J
1							Days to complete			BH 2024/25
2	Address	Notice received date	Expected end date	Pre-work			Working days	Working days - BH		01/04/2024
3				Required	Date completed	Days to complete				06/05/2024
4	Gnome Cave	20/05/2024	16/06/2024	Yes	30/05/2024	10	9	8		27/05/2024
5										26/08/2024
6										25/12/2024
7										26/12/2024
8										01/01/2025
Days between 3 dates (2)

Cell Formulas
Range	Formula
C4	C4	=B4+27
F4	F4	=IF(OR(A4="",D4<>"Yes"),"",IF(E4>1,E4-B4,C4-TODAY()))
G4	G4	=IF(OR(A4="",D4<>"Yes"),"",IF(E4>1,NETWORKDAYS(B4,E4),NETWORKDAYS(TODAY(),C4)))
H4	H4	=IF(OR(A4="",D4<>"Yes"),"",IF(E4>1,NETWORKDAYS(B4,E4,J2:J8),NETWORKDAYS(TODAY(),C4,J2:J8)))

Cells with Data Validation
Cell	Allow	Criteria
D4	List	Yes, No

Appreciate both your help, thank you...

 

[Joe4]

You are welcome.
Glad we were able to help!