Working Days differerence between 2 cells

[Trie1977]

Hi,

I was wondering what the formula is to get the difference of working days between the swings (G column).

2020-03-15 is the last current known swing, 2022-02-10 was the previous swing, and so on.

Thanks in advance.

Trading Data All.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Timeframe	Open	High	Low	Close	Swings	Pips	Opbouw	FibRatios	TimeDiff	Column1
2	2022-03-18	Daily	1,3147	1,3150	1,3145	1,3150			58			FALSE
3	2022-03-17	Daily	1,3146	1,3211	1,3087	1,3149			57			FALSE
4	2022-03-16	Daily	1,3039	1,3157	1,3030	1,3147			31			FALSE
5	2022-03-15	Daily	1,3000	1,3089	1,2999	1,3042	1,2999	-644	-80	-225,5%		#VALUE!
6	2022-03-14	Daily	1,3039	1,3079	1,3001	1,3001			-46			FALSE
7	2022-03-11	Daily	1,3083	1,3125	1,3028	1,3036			-70			FALSE
8	2022-03-10	Daily	1,3175	1,3195	1,3081	1,3084			5			FALSE
9	2022-03-09	Daily	1,3099	1,3190	1,3088	1,3187			45			FALSE
10	2022-03-08	Daily	1,3103	1,3145	1,3082	1,3100			-99			FALSE
11	2022-03-07	Daily	1,3221	1,3243	1,3102	1,3103			-109			FALSE
12	2022-03-04	Daily	1,3344	1,3352	1,3202	1,3231			-66			FALSE
13	2022-03-03	Daily	1,3404	1,3418	1,3318	1,3349			11			FALSE
14	2022-03-02	Daily	1,3318	1,3407	1,3272	1,3405			-31			FALSE
15	2022-03-01	Daily	1,3417	1,3438	1,3302	1,3324			6			FALSE
16	2022-02-28	Daily	1,3309	1,3432	1,3309	1,3420			-7			FALSE
17	2022-02-25	Daily	1,3373	1,3439	1,3367	1,3409			-111			FALSE
18	2022-02-24	Daily	1,3541	1,3549	1,3273	1,3377			-71			FALSE
19	2022-02-23	Daily	1,3574	1,3621	1,3536	1,3547			16			FALSE
20	2022-02-22	Daily	1,3600	1,3605	1,3538	1,3586			-34			FALSE
21	2022-02-21	Daily	1,3586	1,3639	1,3584	1,3600			-4			FALSE
22	2022-02-18	Daily	1,3614	1,3643	1,3573	1,3595			5			FALSE
23	2022-02-17	Daily	1,3582	1,3638	1,3556	1,3614			37			FALSE
24	2022-02-16	Daily	1,3534	1,3601	1,3526	1,3579			34			FALSE
25	2022-02-15	Daily	1,3519	1,3567	1,3487	1,3540			-5			FALSE
26	2022-02-14	Daily	1,3551	1,3572	1,3495	1,3533			-38			FALSE
27	2022-02-11	Daily	1,3553	1,3610	1,3514	1,3560			-34			FALSE
28	2022-02-10	Daily	1,3534	1,3644	1,3523	1,3561	1,3644	286	117	-73,1%		29720
29	2022-02-09	Daily	1,3538	1,3589	1,3527	1,3534			19			FALSE
30	2022-02-08	Daily	1,3535	1,3564	1,3508	1,3548			17			FALSE
31	2022-02-07	Daily	1,3533	1,3551	1,3491	1,3536			-14			FALSE
32	2022-02-04	Daily	1,3596	1,3615	1,3505	1,3527			-33			FALSE
33	2022-02-03	Daily	1,3574	1,3628	1,3538	1,3599			23			FALSE
34	2022-02-02	Daily	1,3516	1,3587	1,3515	1,3576			87			FALSE
35	2022-02-01	Daily	1,3443	1,3528	1,3428	1,3524			41			FALSE
36	2022-01-31	Daily	1,3393	1,3460	1,3387	1,3445			22			FALSE
37	2022-01-28	Daily	1,3379	1,3433	1,3365	1,3395			7			FALSE
38	2022-01-27	Daily	1,3461	1,3468	1,3358	1,3381	1,3358	-391	-167	-66,5%		29710
39	2022-01-26	Daily	1,3493	1,3525	1,3445	1,3465			6			FALSE
40	2022-01-25	Daily	1,3485	1,3519	1,3436	1,3500			-47			FALSE
41	2022-01-24	Daily	1,3551	1,3565	1,3441	1,3490			-37			FALSE
42	2022-01-21	Daily	1,3599	1,3602	1,3546	1,3553			-60			FALSE
43	2022-01-20	Daily	1,3610	1,3662	1,3587	1,3598			13			FALSE
44	2022-01-19	Daily	1,3592	1,3649	1,3587	1,3612			-13			FALSE
45	2022-01-18	Daily	1,3642	1,3662	1,3573	1,3598			-28			FALSE
46	2022-01-17	Daily	1,3674	1,3690	1,3637	1,3645			-53			FALSE
47	2022-01-14	Daily	1,3707	1,3743	1,3653	1,3683			-6			FALSE
48	2022-01-13	Daily	1,3696	1,3749	1,3696	1,3702	1,3749	588	128	-87,3%		29700
49	2022-01-12	Daily	1,3633	1,3714	1,3621	1,3700			59			FALSE
50	2022-01-11	Daily	1,3576	1,3636	1,3562	1,3634			29			FALSE
51	2022-01-10	Daily	1,3580	1,3604	1,3532	1,3575			5			FALSE
52	2022-01-07	Daily	1,3527	1,3598	1,3527	1,3592			37			FALSE
53	2022-01-06	Daily	1,3551	1,3559	1,3490	1,3535			-32			FALSE
54	2022-01-05	Daily	1,3528	1,3599	1,3522	1,3556			63			FALSE
55	2022-01-04	Daily	1,3475	1,3557	1,3459	1,3528			29			FALSE
56	2022-01-03	Daily	1,3529	1,3533	1,3431	1,3477			-35			FALSE
57	2021-12-31	Daily	1,3492	1,3550	1,3465	1,3542			11			FALSE
58	2021-12-30	Daily	1,3487	1,3522	1,3454	1,3502			46			FALSE
59	2021-12-29	Daily	1,3429	1,3500	1,3409	1,3487			-6			FALSE
60	2021-12-28	Daily	1,3440	1,3462	1,3415	1,3429			25			FALSE
61	2021-12-27	Daily	1,3392	1,3445	1,3389	1,3443			5			FALSE
62	2021-12-24	Daily	1,3404	1,3420	1,3385	1,3392			44			FALSE
63	2021-12-23	Daily	1,3347	1,3438	1,3341	1,3411			100			FALSE
64	2021-12-22	Daily	1,3251	1,3363	1,3240	1,3349			43			FALSE
65	2021-12-21	Daily	1,3205	1,3271	1,3198	1,3269			24			FALSE
66	2021-12-20	Daily	1,3240	1,3245	1,3173	1,3209			-57			FALSE
67	2021-12-17	Daily	1,3319	1,3339	1,3231	1,3234			-11			FALSE
68	2021-12-16	Daily	1,3257	1,3375	1,3242	1,3323			70			FALSE
69	2021-12-15	Daily	1,3230	1,3283	1,3172	1,3259			-19			FALSE
70	2021-12-14	Daily	1,3212	1,3256	1,3191	1,3227			-16			FALSE
71	2021-12-13	Daily	1,3246	1,3269	1,3207	1,3212			20			FALSE
72	2021-12-10	Daily	1,3220	1,3276	1,3187	1,3263			17			FALSE
73	2021-12-09	Daily	1,3201	1,3223	1,3171	1,3219			10			FALSE
74	2021-12-08	Daily	1,3241	1,3261	1,3161	1,3202	1,3161	-673	-128	-159,4%		29674
75	2021-12-07	Daily	1,3262	1,3289	1,3209	1,3242			3			FALSE
Time2Swings

Cell Formulas
Range	Formula
L2:L4	L2	=IF(G2<>0,NETWORKDAYS(A:A,A:A))
L5	L5	=IF(G5<>0,NETWORKDAYS(IF(G3:G500,">0",0),A2,A28))
L6:L75	L6	=IF(G6<>0,NETWORKDAYS(IF(G3:G501>0,2999),A3,A29))

Named Ranges
Name	Refers To	Cells
ExternalData_4	=Time2Swings!$A$1:$F$691	L2:L4

 

[Anthony47]

Maybe:

=NETWORKDAYS(LARGE(IF(G1:G1000<>"",A1:A1000,""),ROW(A2)),LARGE(IF(G1:G1000<>"",A1:A1000,""),ROW(A1)))

To be inserted using Contr-Shift-Enter, not Enter alone.
If you have more than 1000 lines then increase those "1000" to 2000 or 5000 or 10000 (a safe value, but avoid 1000000)

The copy down

Bye

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Timeframe	Open	High	Low	Close	Swings	Pips	Opbouw	FibRatios	TimeDiff	Column1
2	18/03/2022	Daily	1.31474	1.31496	1.31451	1.31496			57.7
3	17/03/2022	Daily	1.31462	1.32111	1.30874	1.31493			57
4	16/03/2022	Daily	1.30393	1.31566	1.30304	1.31474			31.1
5	15/03/2022	Daily	1.29996	1.30887	1.29993	1.30421	1.29993	-644.4	-79.8	-2.25472		24
6	14/03/2022	Daily	1.30386	1.30791	1.30005	1.30007			-45.8
7	11/03/2022	Daily	1.30832	1.31249	1.30276	1.30359			-69.7
8	10/03/2022	Daily	1.31753	1.31946	1.30814	1.30836			4.9
9	09/03/2022	Daily	1.30992	1.31897	1.30881	1.31866			45.1
10	08/03/2022	Daily	1.31032	1.31446	1.30821	1.30995			-98.6
11	07/03/2022	Daily	1.32214	1.32432	1.31019	1.31031			-109.1
12	04/03/2022	Daily	1.3344	1.33523	1.32024	1.32308			-65.6
13	03/03/2022	Daily	1.34035	1.34179	1.33176	1.33487			11.1
14	02/03/2022	Daily	1.33181	1.34068	1.32715	1.34045			-30.7
15	01/03/2022	Daily	1.34166	1.34375	1.33023	1.33241			5.9
16	28/02/2022	Daily	1.33092	1.34316	1.33092	1.34196			-7.2
17	25/02/2022	Daily	1.33726	1.34388	1.33667	1.34089			-110.5
18	24/02/2022	Daily	1.35406	1.35493	1.32729	1.33768			-71.2
19	23/02/2022	Daily	1.35741	1.36205	1.35359	1.35471			15.6
20	22/02/2022	Daily	1.35995	1.36049	1.35384	1.35856			-33.7
21	21/02/2022	Daily	1.35859	1.36386	1.3584	1.36			-4.2
22	18/02/2022	Daily	1.36137	1.36428	1.35732	1.3595			4.7
23	17/02/2022	Daily	1.35819	1.36381	1.35557	1.36142			37.2
24	16/02/2022	Daily	1.35335	1.36009	1.35258	1.35794			33.8
25	15/02/2022	Daily	1.3519	1.35671	1.34865	1.35402			-4.8
26	14/02/2022	Daily	1.35506	1.35719	1.34951	1.35326			-37.6
27	11/02/2022	Daily	1.35527	1.36095	1.3514	1.35595			-34.2
28	10/02/2022	Daily	1.35338	1.36437	1.35231	1.35614	1.36437	285.8	116.7	-0.73095		11
29	09/02/2022	Daily	1.35381	1.35894	1.3527	1.35343			19.4
30	08/02/2022	Daily	1.35348	1.35639	1.35076	1.35478			17.1
31	07/02/2022	Daily	1.35326	1.35508	1.34905	1.35355			-14.2
32	04/02/2022	Daily	1.35956	1.36148	1.35047	1.35272			-33.2
33	03/02/2022	Daily	1.35738	1.36278	1.35379	1.35987			22.8
34	02/02/2022	Daily	1.3516	1.35869	1.35151	1.35761			87.2
35	01/02/2022	Daily	1.34433	1.35281	1.34279	1.3524			41.1
36	31/01/2022	Daily	1.33929	1.34603	1.33868	1.34448			21.6
37	28/01/2022	Daily	1.33786	1.34329	1.33652	1.3395			7.3
38	27/01/2022	Daily	1.34607	1.34678	1.33579	1.3381	1.33579	-391	-166.8	-0.66508		11
39	26/01/2022	Daily	1.34932	1.35247	1.34445	1.34651			5.9
40	25/01/2022	Daily	1.34847	1.35188	1.34362	1.35001			-46.6
41	24/01/2022	Daily	1.3551	1.35654	1.34405	1.34898			-36.9
42	21/01/2022	Daily	1.35992	1.36023	1.35458	1.3553			-59.7
43	20/01/2022	Daily	1.361	1.3662	1.35871	1.35984			13.3
44	19/01/2022	Daily	1.35917	1.36487	1.35865	1.36122			-12.9
45	18/01/2022	Daily	1.36423	1.36616	1.3573	1.35977			-28.4
46	17/01/2022	Daily	1.36735	1.369	1.36373	1.36449			-52.8
47	14/01/2022	Daily	1.37074	1.37428	1.36534	1.3683			-6.1
48	13/01/2022	Daily	1.36964	1.37489	1.36964	1.37021	1.37489	587.9	128.1	-0.87316		27
49	12/01/2022	Daily	1.36328	1.37141	1.36208	1.37002			59.2
50	11/01/2022	Daily	1.35758	1.36363	1.35616	1.36335			29.3
51	10/01/2022	Daily	1.35804	1.36037	1.35323	1.35751			5.2
52	07/01/2022	Daily	1.35274	1.35975	1.35271	1.35917			36.9
53	06/01/2022	Daily	1.35514	1.35588	1.34902	1.35345			-32.2
54	05/01/2022	Daily	1.35275	1.35986	1.35224	1.35556			63
55	04/01/2022	Daily	1.34752	1.35574	1.34594	1.35282			28.6
56	03/01/2022	Daily	1.35293	1.35334	1.34308	1.34772			-34.6
57	31/12/2021	Daily	1.34917	1.35504	1.34654	1.35417			11
58	30/12/2021	Daily	1.34868	1.35217	1.34544	1.35023			45.7
59	29/12/2021	Daily	1.34293	1.34996	1.34087	1.3487			-6.1
60	28/12/2021	Daily	1.34402	1.34616	1.34148	1.3429			25.4
61	27/12/2021	Daily	1.3392	1.34451	1.33894	1.34429			4.8
62	24/12/2021	Daily	1.34042	1.34196	1.33846	1.3392			44
63	23/12/2021	Daily	1.33473	1.34377	1.33406	1.34112			100.3
64	22/12/2021	Daily	1.32513	1.33631	1.32403	1.33492			42.7
65	21/12/2021	Daily	1.3205	1.32708	1.31976	1.32691			24.2
66	20/12/2021	Daily	1.32396	1.32447	1.31734	1.32093			-57.3
67	17/12/2021	Daily	1.33187	1.33392	1.32307	1.32343			-10.8
68	16/12/2021	Daily	1.32569	1.33745	1.32415	1.33226			69.7
69	15/12/2021	Daily	1.32301	1.32827	1.31718	1.32589			-19.1
70	14/12/2021	Daily	1.32116	1.32564	1.31909	1.32273			-16.4
71	13/12/2021	Daily	1.32464	1.32687	1.32073	1.32117			19.9
72	10/12/2021	Daily	1.32195	1.32757	1.31874	1.32629			16.6
73	09/12/2021	Daily	1.32005	1.32226	1.31708	1.32185			9.8
74	08/12/2021	Daily	1.32405	1.32612	1.3161	1.32021	1.3161	-673.3	-128.4	-1.59436
75	07/12/2021	Daily	1.32623	1.32894	1.32091	1.32421			3.1
Main

Cell Formulas
Range	Formula
L2:L75	L2	=IF(G2="","",IFERROR(NETWORKDAYS(AGGREGATE(14,6,A3:A$1000/(G3:G$1000<>""),1),A2),""))

 

[Trie1977]

Hi both,

Thanks for the input.

I tried both but don't get the right results; But if I copy the table of Fluff I do get the right results.
What's wrong with my table?

By the way, I did use (Press CTRL+SHIFT+ENTER to enter array formulas.)

Trading Data All.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Timeframe	Open	High	Low	Close	Swings	Pips	Opbouw	FibRatios	Anthony47	Fluff
2	2022-03-18	Daily	1,3147	1,3150	1,3145	1,3150			58		5
3	2022-03-17	Daily	1,3146	1,3211	1,3087	1,3149			57		17
4	2022-03-16	Daily	1,3039	1,3157	1,3030	1,3147			31		10
5	2022-03-15	Daily	1,3000	1,3089	1,2999	1,3042	1,2999	-644	-80	######	10	2
6	2022-03-14	Daily	1,3039	1,3079	1,3001	1,3001			-46		3
7	2022-03-11	Daily	1,3083	1,3125	1,3028	1,3036			-70		2
8	2022-03-10	Daily	1,3175	1,3195	1,3081	1,3084			5		28
9	2022-03-09	Daily	1,3099	1,3190	1,3088	1,3187			45		2
10	2022-03-08	Daily	1,3103	1,3145	1,3082	1,3100			-99		8
11	2022-03-07	Daily	1,3221	1,3243	1,3102	1,3103			-109		2
12	2022-03-04	Daily	1,3344	1,3352	1,3202	1,3231			-66		4
13	2022-03-03	Daily	1,3404	1,3418	1,3318	1,3349			11		17
14	2022-03-02	Daily	1,3318	1,3407	1,3272	1,3405			-31		15
15	2022-03-01	Daily	1,3417	1,3438	1,3302	1,3324			6		4
16	2022-02-28	Daily	1,3309	1,3432	1,3309	1,3420			-7		-2
17	2022-02-25	Daily	1,3373	1,3439	1,3367	1,3409			-111		0
18	2022-02-24	Daily	1,3541	1,3549	1,3273	1,3377			-71		29
19	2022-02-23	Daily	1,3574	1,3621	1,3536	1,3547			16		1
20	2022-02-22	Daily	1,3600	1,3605	1,3538	1,3586			-34		30
21	2022-02-21	Daily	1,3586	1,3639	1,3584	1,3600			-4		-1
22	2022-02-18	Daily	1,3614	1,3643	1,3573	1,3595			5		28
23	2022-02-17	Daily	1,3582	1,3638	1,3556	1,3614			37		2
24	2022-02-16	Daily	1,3534	1,3601	1,3526	1,3579			34		46
25	2022-02-15	Daily	1,3519	1,3567	1,3487	1,3540			-5		5
26	2022-02-14	Daily	1,3551	1,3572	1,3495	1,3533			-38		24
27	2022-02-11	Daily	1,3553	1,3610	1,3514	1,3560			-34		8
28	2022-02-10	Daily	1,3534	1,3644	1,3523	1,3561	1,3644	286	117	-73,1%	20	-22
29	2022-02-09	Daily	1,3538	1,3589	1,3527	1,3534			19		2
30	2022-02-08	Daily	1,3535	1,3564	1,3508	1,3548			17		1
31	2022-02-07	Daily	1,3533	1,3551	1,3491	1,3536			-14		40
32	2022-02-04	Daily	1,3596	1,3615	1,3505	1,3527			-33		-1
33	2022-02-03	Daily	1,3574	1,3628	1,3538	1,3599			23		8
34	2022-02-02	Daily	1,3516	1,3587	1,3515	1,3576			87		2
35	2022-02-01	Daily	1,3443	1,3528	1,3428	1,3524			41		10
36	2022-01-31	Daily	1,3393	1,3460	1,3387	1,3445			22		-1
37	2022-01-28	Daily	1,3379	1,3433	1,3365	1,3395			7		19
38	2022-01-27	Daily	1,3461	1,3468	1,3358	1,3381	1,3358	-391	-167	-66,5%	1	-32
39	2022-01-26	Daily	1,3493	1,3525	1,3445	1,3465			6		11
40	2022-01-25	Daily	1,3485	1,3519	1,3436	1,3500			-47		-1
41	2022-01-24	Daily	1,3551	1,3565	1,3441	1,3490			-37		-1
42	2022-01-21	Daily	1,3599	1,3602	1,3546	1,3553			-60		1
43	2022-01-20	Daily	1,3610	1,3662	1,3587	1,3598			13		4
44	2022-01-19	Daily	1,3592	1,3649	1,3587	1,3612			-13		11
45	2022-01-18	Daily	1,3642	1,3662	1,3573	1,3598			-28		19
46	2022-01-17	Daily	1,3674	1,3690	1,3637	1,3645			-53		#NUM!
47	2022-01-14	Daily	1,3707	1,3743	1,3653	1,3683			-6		#NUM!
48	2022-01-13	Daily	1,3696	1,3749	1,3696	1,3702	1,3749	588	128	-87,3%	#NUM!	-42
49	2022-01-12	Daily	1,3633	1,3714	1,3621	1,3700			59		#NUM!
50	2022-01-11	Daily	1,3576	1,3636	1,3562	1,3634			29		#NUM!
51	2022-01-10	Daily	1,3580	1,3604	1,3532	1,3575			5		#NUM!
52	2022-01-07	Daily	1,3527	1,3598	1,3527	1,3592			37		#NUM!
53	2022-01-06	Daily	1,3551	1,3559	1,3490	1,3535			-32		#NUM!
Time2Swings

Cell Formulas
Range	Formula
K2:K53	K2	=NETWORKDAYS(LARGE(IF(G1:G1000<>"",A1:A1000,""),ROW(A2)),LARGE(IF(G1:G1000<>"",A2:A1000,""),ROW(A1)))
L2:L53	L2	=IF(G2="","",IFERROR(NETWORKDAYS(AGGREGATE(14,6,A3:A$1000/(G3:G$1000<>""),1),A2),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Anthony47]

My formula was intended to extract the list of the results in a separate column (for example in M2:M10), not in column L
Fluff understood better than me your request, for sure he will comment the strange behaviour you reported.

Bye

 

[Fluff]

What's wrong with my table?

Nothing that I can see & if I copy that data it works for me.
Are all the dates in col A sorted in descending order?

 

[Trie1977]

I guess i see the issue here.

I was having a problem;

There were 2 different timeframes, Daily and Weekly. I removed Weekly because these were having weekly dates (2022-3-6, 2022-3-6, etc) instead of days.
Separately: Daily sheet, and Weekly sheet, then it worked out Fluff.

Is there a way to make it work with the weekly timeframe, like if weekly...if daily?

Thanks anyways.

 

[Fluff]

How about

=IF(G2="","",IFERROR(NETWORKDAYS(AGGREGATE(14,6,A3:A$1000/(G3:G$1000<>"")/(B3:B$1000=B2),1),A2),""))

 

[Trie1977]

Yes! I get the right results now.

Thank you so so much!

Have a great week.

 

[Fluff]

You're welcome & thanks for the feedback.