Automated Deadline Alert and Progress tracking formulas?

[Nineta]

Hi there, can someone help me with the formulas I need under the columns Automated Deadline Alert and Progress Tracking as shown below in my Excel table?

Thanks a million!
Nineta

 

[felixstraube]

What do you want to happen in column I?
comparing the end date with the current date from TODAY() function and if it is due show a message?

What do you want as a result in Progress tracking?

 

[Nineta]

Hello and thank you for your question, I am looking into automatically notifying or reminding me when a specific deadline or due date is approaching or has been reached.
In Progress tracking, I am looking into monitoring and updating the status of various tasks or activities as they are being worked on, rather than just tracking deadlines and due dates

I hope this is clearer ..... Thank you!

 

[felixstraube]

How about this?:
I added some example dates. The L4:L5 range is just to show the date of today, it is not necessary.

Book1
	E	F	G	H	I	J	K	L
1	Start Date	End Date	Duration			Automated Deadline Alert
2	09/01/2024 Tue	29/02/2024 Thu	38			7.9%
3	19/03/2024 Tue	17/04/2024 Wed	22			Not started
4	11/12/2023 Mon	05/01/2024 Fri	20			100.0% - Over due!!!		Today
5	12/12/2023 Tue	08/01/2024 Mon	20			100.0% - Over due!!!		11/01/2024 Thu
6	13/12/2023 Wed	09/01/2024 Tue	20			100.0% - Over due!!!
7	14/12/2023 Thu	10/01/2024 Wed	20			100.0% - Over due!!!
8	15/12/2023 Fri	11/01/2024 Thu	20			100.0% - Due today!!!
9	18/12/2023 Mon	12/01/2024 Fri	20			95.0% (< 10 days left, < 10% left)
10	19/12/2023 Tue	15/01/2024 Mon	20			90.0% (< 10 days left)
11	20/12/2023 Wed	16/01/2024 Tue	20			85.0% (< 10 days left)
12	21/12/2023 Thu	17/01/2024 Wed	20			80.0% (< 10 days left)
13	22/12/2023 Fri	18/01/2024 Thu	20			75.0% (< 10 days left)
14	25/12/2023 Mon	19/01/2024 Fri	20			70.0% (< 10 days left)
15	26/12/2023 Tue	22/01/2024 Mon	20			65.0% (< 10 days left)
16	27/12/2023 Wed	23/01/2024 Tue	20			60.0% (< 10 days left)
17	28/12/2023 Thu	24/01/2024 Wed	20			55.0% (< 10 days left)
18	29/12/2023 Fri	25/01/2024 Thu	20			50.0%
19	01/01/2024 Mon	26/01/2024 Fri	20			45.0%
20	02/01/2024 Tue	29/01/2024 Mon	20			40.0%
21	03/01/2024 Wed	30/01/2024 Tue	20			35.0%
22	04/01/2024 Thu	31/01/2024 Wed	20			30.0%
23	05/01/2024 Fri	01/02/2024 Thu	20			25.0%
24	08/01/2024 Mon	02/02/2024 Fri	20			20.0%
25	09/01/2024 Tue	05/02/2024 Mon	20			15.0%
26	10/01/2024 Wed	06/02/2024 Tue	20			10.0%
27	11/01/2024 Thu	07/02/2024 Wed	20			5.0%
28	12/01/2024 Fri	08/02/2024 Thu	20			Not started
29	15/01/2024 Mon	09/02/2024 Fri	20			Not started
30	16/01/2024 Tue	12/02/2024 Mon	20			Not started
Sheet1

Cell Formulas
Range	Formula
G2:G30	G2	=NETWORKDAYS(E2,F2)
F4:F30	F4	=WORKDAY(E4,19)
E5:E30	E5	=WORKDAY(E4,1)
L5	L5	=TODAY()
J2:J30	J2	=LET( s,$E2, e,$F2, t,TODAY(), nwdTotal,NETWORKDAYS(s,e), nwdUsed,IF(t>=s,NETWORKDAYS(s,t),0), nwdLeft,nwdTotal-nwdUsed, progress,MIN(1-nwdLeft/nwdTotal,1), message1,IF(nwdLeft<10,"< 10 days left",""), message2,IF(progress>0.9,", < 10% left",""), IF(nwdUsed<=0, "Not started",TEXT(progress,"0.0%")&IF(nwdLeft<0," - Over due!!!",IF(nwdLeft=0," - Due today!!!",SUBSTITUTE(" ("&message1&message2&")","()",""))) ))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:F30	Expression	=E2=TODAY()	text	NO