User Posts: Aniruddah Alam
How to Create a Database in Excel with Pictures (with Easy Steps)

In this article, I am going to show how to create a database in Excel with pictures. Even though MS Excel is not an ideal application to build a database, we ...

Excel VBA Text to Columns (4 Easy Examples)

In this article, we are going to discuss the Excel VBA Text to Columns method (TextToColumns). The TextToColumns method works the same way as the Text to ...

Excel VBA Comment Block (4 Easy Methods)

Comments are vital components of a code as they guide the readers to understand the context, functionality, and purpose of the written code. In this article, ...

VBA Animation in UserForm (4 Suitable Examples)

In this article, I will demonstrate various examples of VBA animation in UserForm in Excel. These examples will not only enhance the aesthetics and visual ...

[Fixed!] Invalid Forward Reference in VBA

Handling errors is always a tedious task in Excel VBA. But things get really difficult when we don’t know exactly what is causing the error. In this article, ...

How to Use Macro to Create Folders from Excel List (3 Methods)

In this article, I am going to demonstrate how we can use VBA Macro to create folders from an Excel list. Multiple folders and subfolders with various names ...

How to Use Excel VBA to List Files in Folder (4 Easy Methods)

In this article, I'll demonstrate how to use Excel VBA to List Files in Folder. Basically, it will compile a list of all the file names in a folder. On many ...

How to Use Excel SUMIF with Blank Cells (3 Easy Methods)

This article illustrates how to use the SUMIF function to sum up values corresponding to blank or empty cells in Excel. Even though blank cells are usually ...

How to Use Excel VBA InputBox with Example

In many situations, while working in Excel VBA, we need to ask the user to insert some parameters directly. We can do that using VBA InputBox. If you are ...

How to Use Excel VBA Nested For Loop (3 Useful Examples)

In this article, I am going to introduce you to one of the most used tools in Excel VBA and that is Nested For Loop. If you already know the uses of For loops, ...

[Solved!] Formulas to Compare Dates in Excel Not Working

In this article, I am going to discuss some possible cases when the formulas to compare dates are not working in Excel and devise some solutions to the ...

[Solved!] Excel Queries and Connections Not Working

In this article, I will discuss some possible solutions to Excel Queries and Connections not working. Excel Queries and Connections is a powerful tool when it ...

How to Create Bar Chart with Error Bars in Excel (4 Easy Methods)

In this article, I will show you how to create bar chart with Error Bars in Excel. While representing different scientific measurements and data in charts, it ...

How to Create Floating Cells in Excel (2 Easy Methods)

In this article, I will show how to create floating cells in Excel. In many situations, we need to display a group of cells always floating on the screen while ...

How to Use Macaulay Duration Formula in Excel (2 Easy Methods)

In this article, I will show you how to use the Macaulay Duration formula in Excel. While performing many financial calculations, especially while dealing with ...

Browsing All Comments By: Aniruddah Alam
  1. 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.
    1-VBA Code for Custom Function FormatDate
    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")
        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.
    2-Custom Function Used in Worksheet
    Hopefully, it will solve your problem. If you face problems anymore, feel free to post them on our Exceldemy Forum.

  2. Thank you for your inquiry, INGE. It is not really clear from your inquiry what you want. I believe you want to know how we can calculate the running total when a month’s entry is zero. In that situation, the Pivot table will add 0 to the previous running total, displaying the same prior running total as before. I hope this clarifies your concerns. If you have any further queries or wish to share your documents, please post them on our Exceldemy Forum (

  3. 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( We will do our best to find a solution.

  4. Thank you Heidi for your comment. You can paste the following VBA code into your Desired Worksheet on the VBA window. This code will automatically update the current date in the corresponding cell of column N if anything is changed in columns O:V. Hope, it will solve your problem. If you have any further queries, you can post them on our Exceldemy Forum.

    Team Exceldemy

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        If Not Intersect(Target, Range("O:V")) Is Nothing Then
            For Each cell In Intersect(Target, Range("O:V")).Cells
                If cell.Row <= 400 Then
                    Range("N" & cell.Row).Value = Date
                End If
            Next cell
        End If
    End Sub
  5. 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.

    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
                End With
                Set Mail_Item = Nothing
            End If
        End If
        Set Outlook_App_Create = Nothing
    End Sub
  6. Thank you for your query, DUONG. The third, fifth, and seventh approaches mentioned above can be used safely when numerous people share the same mark. However, their rankings on the Top 10 list are based on where they actually stand on the unsorted list. For instance, Jessica, Henderson, and Aaron will be in positions 5, 6, and 7, respectively, in the Top 10 list, if they all receive 70 marks. I believe Hope you got your answer. Please ask on our ExcelDemy forum if you have any additional questions.
    Dynamic List With Multiple Persons Having Same Mark

  7. 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.

    Sum Count by cell Colors Comment-1

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

    User Defined Function to Sum by Font Color & Date

    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
        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).

    Results of applying User Defined Function

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

    Ultimate Results after Applying User Defined Function

    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.

  8. Hi Keaton, thanks for your query. As the code has no limitation on the number of rows, it should work in your case. Maybe the code worked for the first 109 rows only because you only selected the first 109 rows in the prompt. Kindly select the entire dataset while running the code. Hopefully, it will do the job for you. If the code still doesn’t work, you can share your file using our Exceldemy forum(

  9. 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.

Advanced Excel Exercises with Solutions PDF