Not Due Until & (Over)due

[jhum]

Hi there,

I want to create a worksheet that tells me how much time has past or is there until action is needed.

So there is an appointment which needs to be taken every 2 years.
I want to see how much time in months is left until they need to take it again or how many months day are overdue (likewise the red and green example i just made up myself).

Name	Training 1	Training 2	Training 3	Training 4
John	01.10.2022	23					Months until next training
Sarah	01.08.2018	01.08.2020	01.08.2022	-1			Months overdue
Samira	01.03.2020	15.05.2022	19

Thank you for you help!

 

[etaf]

you probably need a macro to update the cell next to the training as shown
However you could use a function if you would have the cell next to name

will something like this - Dates are when the training was taken and then Column B gives the date of the next training and Column C number of months (rounded down) so may not be exactly what you wanted

and then the firs blank cell is colour coded , but you could highlight other cells if necessary

otherwise as i say, you may need VBA , which i do not provide solutions for

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	name	training due	Months	training 1	training 2	training 3	training 4	training 5	training 6	training 7	training 8	training 9
2	Emp1	10/12/24	24	10/12/22
3	Emp2	8/1/24	22	1/1/20	1/1/21	8/1/22
4	Emp3	1/1/23	3	1/1/21
5	Emp4	8/16/22	-1	9/1/19	8/16/20
6	Emp5	7/1/24	21	7/1/22
7	Emp6
8	Emp7
9
10
11
Sheet3

Cell Formulas
Range	Formula
B2:B11	B2	=IF(OR(D2="",A2=""),"",DATE(YEAR(MAX(D2:L2))+2,MONTH(MAX(D2:L2)),DAY(MAX(D2:L2))))
C2:C11	C2	=IF(OR(A2="",D2=""),"",IF(B2<=TODAY(),DATEDIF(B2,TODAY(),"m")*-1,DATEDIF(TODAY(),B2,"m")))
E5	E5	=DATE(YEAR(TODAY())-2,MONTH(TODAY())-1,DAY(TODAY())-10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:L6	Expression	=AND($C2<0,C2<>"",D2="")	text	NO
D2:L6	Expression	=AND($C2>0,C2<>"",D2="")	text	NO

 

[jhum]

Thank you

you probably need a macro to update the cell next to the training as shown
However you could use a function if you would have the cell next to name

will something like this - Dates are when the training was taken and then Column B gives the date of the next training and Column C number of months (rounded down) so may not be exactly what you wanted

and then the firs blank cell is colour coded , but you could highlight other cells if necessary

otherwise as i say, you may need VBA , which i do not provide solutions for

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	name	training due	Months	training 1	training 2	training 3	training 4	training 5	training 6	training 7	training 8	training 9
2	Emp1	10/12/24	24	10/12/22
3	Emp2	8/1/24	22	1/1/20	1/1/21	8/1/22
4	Emp3	1/1/23	3	1/1/21
5	Emp4	8/16/22	-1	9/1/19	8/16/20
6	Emp5	7/1/24	21	7/1/22
7	Emp6
8	Emp7
9
10
11
Sheet3

Cell Formulas
Range	Formula
B2:B11	B2	=IF(OR(D2="",A2=""),"",DATE(YEAR(MAX(D2:L2))+2,MONTH(MAX(D2:L2)),DAY(MAX(D2:L2))))
C2:C11	C2	=IF(OR(A2="",D2=""),"",IF(B2<=TODAY(),DATEDIF(B2,TODAY(),"m")*-1,DATEDIF(TODAY(),B2,"m")))
E5	E5	=DATE(YEAR(TODAY())-2,MONTH(TODAY())-1,DAY(TODAY())-10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:L6	Expression	=AND($C2<0,C2<>"",D2="")	text	NO
D2:L6	Expression	=AND($C2>0,C2<>"",D2="")	text	NO

Thank you so much - that works just fine! I just don't know how I never gotten the idea to have the cell in front - that also makes it easier to put them in order as who's next!

 

[etaf]

you are welcome