Deadline Reminder

[SSr00]

I am trying to make a user friendly deadline reminder for the shared drive at work, I got it to work with Microsoft 365 but we only have Excel 2016 at work and that does not have the =TEXTJOIN function.
I need whatever course is listed in B Column, to show up on either the "Due Today" or "Due Tomorrow" based on the deadline date set in column C.

Any help is greatly appreciated, thanks.

CourseAuto.xlsx
	A	B	C		E	F
5
6					Due Today	Driver's Cse, Mod3B
7
8
9					Due Tomorrow	CEF
10
11
12		Course	Unit - NLT
13
14		Driver's Cse	11/09/2023
15		CEF	12/09/2023
16		CommOP	02/09/2023
17		BTA	31/08/2023
18		GAA Coaching	27/09/2023
19		Mod2B	01/09/2023
20		Mod3B	11/09/2023
21
22
23
24
25
26
27
28
29
30
31
32
Sheet1

Cell Formulas
Range	Formula
F6	F6	=TEXTJOIN(", ", TRUE, IF(C13:C39 = TODAY(), IF(B13:B39<>"", B13:B39, ""), ""))
F9	F9	=TEXTJOIN(", ", TRUE, IF(C13:C39 = TODAY() +1, IF(B13:B39<>"", B13:B39, ""), ""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C25:C43,B13:C24	Expression	=ISBLANK(C13)	text	NO
C25:C43,B13:C24	Expression	=TODAY()>= B13	text	NO
C25:C43,B13:C24	Expression	=TODAY()>=B13 -1	text	NO

 

[Jeffrey Mahoney]

I know this is not ideal, but it will give you want you want. The code below is a UDF. It must be copied into a Standard VBA module

Book2
	B	C	D	E	F	G	H	I
2				Due Today	Driver's Cse, Mod3B		Driver's Cse, Mod3B
3
4
5				Due Tomorrow			CEF
6
7
8	Course	Unit - NLT
9
10	Driver's Cse	9/11/2023
11	CEF	9/12/2023
12	CommOP	9/2/2023
13	BTA	8/31/2023
14	GAA Coaching	9/27/2023
15	Mod2B	9/1/2023
16	Mod3B	9/11/2023
Sheet2

Cell Formulas
Range	Formula
F2	F2	=TEXTJOIN(", ", TRUE, IF(C10:C36 = TODAY(), IF(B10:B36<>"", B10:B36, ""), ""))
H2	H2	=JoinText(B10:B16,C10:C16,TODAY())
F5	F5	=TEXTJOIN(", ", TRUE, IF(C13:C39 = TODAY() +1, IF(B13:B39<>"", B13:B39, ""), ""))
H5	H5	=JoinText(B10:B16,C10:C16,TODAY()+1)

Function JoinText(Rng1 As Range, Rng2 As Range, DC As Date) As String

  Dim Cel As Range
  Dim Cel2 As Range
  Dim Val1 As String
  Dim Dt As Date
  Dim j As String
  
  For Each Cel In Rng1
    Val1 = Cel.Value
    If Val1 <> "" Then
      Dt = Intersect(Cel.EntireRow, Rng2).Value
      If Dt = DC Then
        If Len(j) > 0 Then
          j = j & ", " & Val1
        Else
          j = Val1
        End If
      End If
    End If
  Next Cel
  JoinText = j
  
    
End Function

 

[SSr00]

You are a legend, thanks so much!!

 

[Jeffrey Mahoney]

Oooh, a legend. That either implies I have skills or I'm old. Both actually might apply relative to your skills and age!