Thanks, LOKESH for your query. In Excel, if you want to convert different date formats into a specific date format with a single formula, the formula will be rather long and complicated. However, you can create a custom function using the following VBA code to do your task.

Code Syntax:

Function FormatDate(cell As Range)
Value = cell.Value
If IsDate(Value) Then
    FormatDate = Format(Value, "dd mmm yyyy")
ElseIf Len(Value) = 5 Then
    Value = DateSerial(Right(Value, 2) + 2000, Mid(Value, 2, 2), Left(Value, 1))
    FormatDate = Format(Value, "dd mmm yyyy")
ElseIf Len(Value) = 6 Then
    Value = DateSerial(Right(Value, 2) + 2000, Mid(Value, 3, 2), Left(Value, 2))
    FormatDate = Format(Value, "dd mmm yyyy")
ElseIf Len(Value) = 7 Then
    Value = DateSerial(Right(Value, 4), Mid(Value, 2, 2), Left(Value, 1))
    FormatDate = Format(Value, "dd mmm yyyy")
ElseIf Len(Value) = 8 Then
    x = DateSerial(Right(Value, 4), Mid(Value, 3, 2), Left(Value, 2))
    If Year(x) < 1900 Then
        x = DateSerial(Left(Value, 4), Mid(Value, 5, 2), Right(Value, 2))
    End If
    FormatDate = Format(x, "dd mmm yyyy")
Else
    FormatDate = "Format Not recognised"
End If
End Function

Here, I have created a Custom Function named FormatDate. Then, I apply the function to different date formats that you provided. Here is the result.

Hopefully, it will solve your problem. If you face problems anymore, feel free to post them on our Exceldemy Forum.
Regards
Aniruddah

Hello, Elena.
Thank you for your kind words. Now, what I can understand from your query is that when you navigate through the preview results, you can only see just one individual’s information rather than everyone’s information. If you exactly follow the steps outlined above, you should be able to see all of the persons’ information in the preview result. Hence, I encourage that you carefully follow the instructions outlined above and observe whether or not your problem is resolved. If your issue persists, you can upload your file to our Exceldemy Forum(https://exceldemy.com/forum/). We will do our best to find a solution.

Thank you JIGNESH for reaching out. Here is a VBA Macro code that will take “To Email Address”, “Email ID CC”, “Subject”, and “Date” ranges from the user and if the code finds any Date which is greater than the Current Date, an email will be sent to the corresponding mail address and CC address with corresponding Subject line. If you have any further queries, feel free to post on our ExcelDemy Forum.
Regards
Aniruddah

Public Sub SendEmailWhenDue()
    Dim Date_Range, Mail_Cc, Mail_Subject As Range
    Dim Mail_Recipient As Range
    Dim Email_Text As Range
    Dim Outlook_App_Create As Object
    Dim Mail_Item As Object
    Dim Last_Row As Long
    Dim VB_CR_LF, Email_Body, Date_Range_Value, Send_Value, Subject As String
    Dim i As Long
    Set Date_Range = Application.InputBox("Please choose the date range:", "Insert Date Range", Type:=8)
    If Date_Range Is Nothing Then Exit Sub
    Set Mail_Recipient = Application.InputBox("Please select the range of Email addresses:", "Insert Mail Recipeint", Type:=8)
    If Mail_Recipient Is Nothing Then Exit Sub
    Set Mail_Cc = Application.InputBox("Please choose the CC range:", "Insert CC Range", Type:=8)
    Set Mail_Subject = Application.InputBox("Please choose the Subject range:", "Insert Date Range", Type:=8)
    Last_Row = Date_Range.Rows.Count
    Set Date_Range = Date_Range(1)
    Set Mail_Recipient = Mail_Recipient(1)
    Set Mail_Cc = Mail_Cc(1)
    Set Mail_Subject = Mail_Subject(1)
    Set Outlook_App_Create = CreateObject("Outlook.Application")
    For i = 1 To Last_Row
        Date_Range_Value = ""
        Date_Range_Value = Date_Range.Offset(i - 1).Value
        If Date_Range_Value <> "" Then 'Condition for sending mail.
        If CDate(Date_Range_Value) <= Date Then
            Send_Value = Mail_Recipient.Offset(i - 1).Value
            Cc = Mail_Cc.Offset(i - 1).Value
            Subject = Mail_Subject.Offset(i - 1).Value
            Email_Body = " Hi, <br> Please check the reminder." 'Compose your Body Here
            Set Mail_Item = Outlook_App_Create.CreateItem(0)
            With Mail_Item
                .Subject = Subject
                .Cc = Cc
                .To = Send_Value
                .HTMLBody = Email_Body
                .Display
            End With
            Set Mail_Item = Nothing
        End If
    End If
    Next
    Set Outlook_App_Create = Nothing
End Sub

Thank you Paul for reaching out. For your special case, it is possible to make a custom function that sums up all the numbers within a specific date range and has specific font color. To do this, we have to pass two more arguments in the function (Starting_Date & Ending_Date). For illustration, I have taken another data set that contains Dates on the column header as you suggested.

I have written another code to create a User-defined Function named SumByDateColor.

Function SumByDateColor(starting_date As Variant, ending_date As Variant, ref_color As Range, sum_range As Range) As Double
    Dim cell_color As Long, sum_cell As Double
    Dim cell As Range
    Application.Volatile
    sum_cell = 0
    cell_color = ref_color.Font.colorIndex
    'iterating through columns
    For i = 1 To sum_range.Columns.Count
        If (sum_range.Cells(1, i) >= starting_date And sum_range.Cells(1, i) <= ending_date) Then
            For j = 2 To sum_range.Rows.Count
            'iterating through rows from each column
                Set cell = sum_range.Cells(j, i)
                    If cell_color = cell.Font.colorIndex Then
                        sum_cell = sum_cell + cell.Value
                    End If
            Next j
        End If
    Next i
    SumByDateColor = sum_cell
End Function

In cell L5, if we apply the function, it will return the sum of all the black font numbers from the first three columns (From 03/03/23 to 03/05/23).
=SUMBYDateColor(I5,J5,K5,$B$4:$G$10)

In this way, we can apply the function for L6: L9 as well and get the following result.

I hope it will solve your problem. If you have any further queries or need the work file, you can ask in our Exceldemy Forum.
Regards
Aniruddah

Thanks, Janel, for your comment. To find all the data of Emily and Jenifer at once, you can utilize the Filter feature of the Excel table shown in method 4 (Return Multiple Values by Using Excel Defined Table). Here, after clicking the down arrow icon, you need to check both Emily and Jenifer in the filter option to get the hobby list of both persons. Hopefully, it will solve your problem.