Calculate workdays from a specific date

[LBee]

Hi all

I have this sheet, where I'm counting tickets that are created x workdays ago (F6) from a specifik date (F5)

The formula i column C are like this =NETWORKDAYS(B2;$F$5), and the formula in cell F8 to F12 are like this =COUNTIFS(C:C;">"&$F$6;A:A;$E8)
This is working fine, but I'm trying to avoid the need of creating column C, and make a formula in F8 to F12 that does the all of the calculation.

Is that possible (and does it make sense)?

 

[GraH]

Hi LBee,
please use the XL2BB add-on for sharing sample data. Beats pictures every day. Most members do not like to type over data from a picture.

 

[Haree]

Hi @LBee
Reverse calculation can help in the above scenario. For Instance from the sample you have shown above, Any ticket created on or before 28/01/2022 will match your criteria. You can tweak your countifs for tickets created on or before 28/01/2022.

But as @GraH mentioned we can check the data and update exactly if you show us a sample through XL2BB
Regards

 

[LBee]

Hi GraH

Sure - never used it before, but currently reading the guide
I will update the post

 

[LBee]

Not sure if this is correct way to do it

Book1
	A	B	C	D	E	F	G
1	Name	TicketCreated	WorkdaysOld
2	Sam	2022-01-01	65
3	Matt	2022-02-06	40
4	Matt	2022-01-26	48
5	Sam	2022-03-15	14		StartDate	2022-04-01
6	Sam	2022-03-27	5		WorkdaysOldMax	45
7	Matt	2022-02-28	25
8	Matt	2022-02-14	35		George	1
9	Jennifer	2022-03-15	14		Matt	6
10	Jennifer	2022-02-23	28		Ann	3
11	Sam	2022-01-06	62		Sam	2
12	Sam	2022-03-15	14		Jennifer	2
13	Ann	2022-02-17	32
14	Jennifer	2022-02-24	27
15	George	2022-03-05	20
16	Ann	2022-03-26	5
17	Matt	2022-01-25	49
18	Sam	2022-02-16	33
19	George	2022-02-14	35
20	Jennifer	2022-03-28	5
21	Jennifer	2022-03-01	24
22	George	2022-03-08	19
23	Jennifer	2022-02-23	28
24	Matt	2022-01-05	63
25	Sam	2022-03-20	10
26	Jennifer	2022-01-23	50
27	Jennifer	2022-01-18	54
28	George	2022-02-22	29
29	Matt	2022-01-27	47
30	Matt	2022-03-19	10
31	Ann	2022-01-24	50
32	Ann	2022-01-28	46
33	Matt	2022-01-05	63
34	Ann	2022-02-25	26
35	George	2022-01-23	50
36	Matt	2022-02-13	35
37	Jennifer	2022-03-03	22
38	Matt	2022-02-01	44
39	Ann	2022-02-10	37
40	Ann	2022-03-10	17
41	Matt	2022-01-27	47
42	Ann	2022-01-19	53
43	Sam	2022-02-11	36
44	Sam	2022-03-20	10
45	Matt	2022-03-15	14
46	Jennifer	2022-02-01	44
47	Ann	2022-03-21	10
48	George	2022-02-03	42
49	Sam	2022-03-25	6
50	Sam	2022-02-23	28
51	Ann	2022-03-31	2
52
Sheet2

Cell Formulas
Range	Formula
F8:F12	F8	=COUNTIFS(C:C,">"&$F$6,A:A,$E8)
C2:C51	C2	=NETWORKDAYS(B2,$F$5)

 

[GraH]

Totally! Now we can help.

 

[GraH]

Following @Haree's tip

Book1
	E	F	G
5	StartDate	1/04/2022	28/01/2022
6	WorkdaysOldMax	45
7
8	George	1
9	Matt	6
10	Ann	3
11	Sam	2
12	Jennifer	2
Sheet2

Cell Formulas
Range	Formula
G5	G5	=WORKDAY(F5,-F6)
F8:F12	F8	=COUNTIFS(B:B,"<="&$G$5,A:A,$E8)

or without the extra "helper":

=COUNTIFS(B:B,"<="&WORKDAY($F$5,-$F$6),A:A,$E8)

 

[LBee]

Thanks a lot both of you - I can confirm it working

/LBee