Length of Service - Flag 5, 10, 15 years etc over 12 months

[thedeadzeds]

Hi Guys,

I am trying to creating a workbook that will show me over the next 12 months, if an employee hits 5, 10, 15, 20, 25 or 30+ years of service.

Something like the below format would be great.

For example, I can see if May 22 Chris will hit 5 years, In June Rhys will hit 10 years, in Sept Steve will hit 15 years and so on. Is this possible?

It doesn't necessarily have to be in the below format, I just need it to be in a table so I can pull it into Power BI.

Many Thanks

Name

Start Date

Length of Service

May-22

Jun-22

Jul-22

Aug-22

Sep-22

Oct-22

Nov-22

Dec-22

Jan-23

Feb-23

Mar-23

Apr-23

May-23

Dave

04/10/2012​

9

10

Jim

19/09/2021​

0

Steve

20/09/2006​

15

15

Chris

21/05/2017​

4

5

Em

13/08/2000​

21

Ian

06/11/1996​

25

25

Tom

06/07/2021​

0

Rhys

02/06/2012​

9

10

 

[Gerald Higgins]

Looks like Steve would actually have hit 15 years in Sep 21, not Sep 22.
Similarly with Ian, hitting 25 years in Nov 21, not Nov 22.
Or is it more complicated than that ?

 

[JamesCanale]

I changed some of the start date years for reasons.

MrExcelPlayground8-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Start Date	Length of Service	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22	Jan 23	Feb 23	Mar 23	Apr 23	May 23
2	Dave	2012-10-04	9.6						10
3	Ji	2021-09-19	0.6
4	Steve	2007-09-20	14.6					15
5	Chris	2017-05-21	5.0	5
6	Em	2000-08-13	21.7
7	Ian	1997-11-06	24.5							25
8	Tom	2021-07-06	0.8
9	Rhys	2012-06-02	9.9		10
Sheet23

Cell Formulas
Range	Formula
D1	D1	=EOMONTH(TODAY(),-1)+1
E1:P1	E1	=EOMONTH(D1,0)+1
C2:C9	C2	=(TODAY()-B2)/365.25
D2:P9	D2	=IF(AND(MONTH(D$1)=MONTH($B2),MOD(YEAR(D$1)-YEAR($B2),5)=0),YEAR(D$1)-YEAR($B2),"")

 

[thedeadzeds]

I changed some of the start date years for reasons.

MrExcelPlayground8-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Start Date	Length of Service	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22	Jan 23	Feb 23	Mar 23	Apr 23	May 23
2	Dave	2012-10-04	9.6						10
3	Ji	2021-09-19	0.6
4	Steve	2007-09-20	14.6					15
5	Chris	2017-05-21	5.0	5
6	Em	2000-08-13	21.7
7	Ian	1997-11-06	24.5							25
8	Tom	2021-07-06	0.8
9	Rhys	2012-06-02	9.9		10
Sheet23

Cell Formulas
Range	Formula
D1	D1	=EOMONTH(TODAY(),-1)+1
E1:P1	E1	=EOMONTH(D1,0)+1
C2:C9	C2	=(TODAY()-B2)/365.25
D2:P9	D2	=IF(AND(MONTH(D$1)=MONTH($B2),MOD(YEAR(D$1)-YEAR($B2),5)=0),YEAR(D$1)-YEAR($B2),"")

This is amazing thank you so much. If needed, is it possible to select random years rather than a multiple of 5? For example, 5, 10, 12, 15, 18, 21, 22, 25
Many thanks

 

[JamesCanale]

I don't have excel handy now....
The part where I have MOD(year-year),5)=0 does the 5 year thing. I use MOD a lot.
You might make it OR(year-year={5,10,12,15,18,21,22,25}). I'll have to try that later.

 

[JamesCanale]

MrExcelPlayground8-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Start Date	Length of Service	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22	Jan 23	Feb 23	Mar 23	Apr 23	May 23
2	Dave	2012-10-04	9.6						10
3	Ji	2021-09-19	0.6
4	Steve	2007-09-20	14.6					15
5	Chris	2017-05-21	5.0	5
6	Em	2000-08-13	21.7				22
7	Ian	1997-11-06	24.5							25
8	Tom	2021-07-06	0.9
9	Rhys	2012-06-02	9.9		10
Sheet23

Cell Formulas
Range	Formula
D1	D1	=EOMONTH(TODAY(),-1)+1
E1:P1	E1	=EOMONTH(D1,0)+1
C2:C9	C2	=(TODAY()-B2)/365.25
D2:P9	D2	=IF(AND(MONTH(D$1)=MONTH($B2),OR(YEAR(D$1)-YEAR($B2)={5,10,12,15,18,21,22,25})),YEAR(D$1)-YEAR($B2),"")

 

[thedeadzeds]

MrExcelPlayground8-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Start Date	Length of Service	May 22	Jun 22	Jul 22	Aug 22	Sep 22	Oct 22	Nov 22	Dec 22	Jan 23	Feb 23	Mar 23	Apr 23	May 23
2	Dave	2012-10-04	9.6						10
3	Ji	2021-09-19	0.6
4	Steve	2007-09-20	14.6					15
5	Chris	2017-05-21	5.0	5
6	Em	2000-08-13	21.7				22
7	Ian	1997-11-06	24.5							25
8	Tom	2021-07-06	0.9
9	Rhys	2012-06-02	9.9		10
Sheet23

Cell Formulas
Range	Formula
D1	D1	=EOMONTH(TODAY(),-1)+1
E1:P1	E1	=EOMONTH(D1,0)+1
C2:C9	C2	=(TODAY()-B2)/365.25
D2:P9	D2	=IF(AND(MONTH(D$1)=MONTH($B2),OR(YEAR(D$1)-YEAR($B2)={5,10,12,15,18,21,22,25})),YEAR(D$1)-YEAR($B2),"")

This is amazing thank you so much for your help