Accounting percentage(completing of 100%) of date values and ignore anything else in whole column

%3CLINGO-SUB%20id%3D%22lingo-sub-3240576%22%20slang%3D%22en-US%22%3EAccounting%20percentage(completing%20of%20100%25)%20of%20date%20values%20and%20ignore%20anything%20else%20in%20whole%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3240576%22%20slang%3D%22en-US%22%3E%3CP%3EPeace%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20account%20the%20parentage%20of%20completing%20of%20a%20values%20in%20a%20column.%3C%2FP%3E%3CP%3EIf%20a%20%3CSTRONG%3Edate%26nbsp%3B%3C%2FSTRONG%3E%26nbsp%3Bis%20inputted%20then%20account%20it%20as%20a%20part%20of%20100%25%20%2F%20111(for%20example).%3C%2FP%3E%3CP%3E%3CSTRONG%3E(title%20of%20column%3A%20an%20email%20sent%20to%20the%20person%20on%20the%20date%20of...)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ethe%20whole%20cells%20of%26nbsp%3Bcolumn%20are%20111.%3C%2FP%3E%3CP%3E%3CSTRONG%3Eif%20anything%20else%20inputted%20in%20the%26nbsp%3Bcolumn%26nbsp%3Bor%20still%20blank%2C%20so%26nbsp%3Bdon't%20account%20it.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E---------%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%201st%20cell%20in%20the%20column%20in%20my%20file%20is%20%3A%20%3CSTRONG%3EN2%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EThe%20location%20of%20cell%20which%20account%20the%20percentage%20%3A%20%3CSTRONG%3EN113%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3240576%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3240803%22%20slang%3D%22en-US%22%3ERe%3A%20Accounting%20percentage(completing%20of%20100%25)%20of%20date%20values%20and%20ignore%20anything%20else%20in%20whole%20colum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3240803%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F919647%22%20target%3D%22_blank%22%3E%40Aadel_AlYahya%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20does%20not%20have%20a%20built-in%20method%20to%20determine%20whether%20a%20cell%20contains%20a%20date.%20You%20can%20use%20the%20following%20custom%20VBA%20function%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20CountDates(rng%20As%20Range)%20As%20Long%0A%20%20%20%20Dim%20cel%20As%20Range%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20For%20Each%20cel%20In%20rng%0A%20%20%20%20%20%20%20%20CountDates%20%3D%20CountDates%20-%20IsDate(cel.Value)%0A%20%20%20%20Next%20cel%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20N113%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCountDates(N2%3AN112)%2F111%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFormat%20N113%20as%20a%20percentage.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Peace

 

I want to account the parentage of completing of a values in a column.

If a date  is inputted then account it as a part of 100% / 111(for example).

(title of column: an email sent to the person on the date of...)

the whole cells of column are 111.

if anything else inputted in the column or still blank, so don't account it.

---------

The 1st cell in the column in my file is : N2.

The location of cell which account the percentage : N113

 

Thanks.

1 Reply

@Aadel_AlYahya 

Excel does not have a built-in method to determine whether a cell contains a date. You can use the following custom VBA function:

Function CountDates(rng As Range) As Long
    Dim cel As Range
    On Error Resume Next
    For Each cel In rng
        CountDates = CountDates - IsDate(cel.Value)
    Next cel
End Function

In N113:

=CountDates(N2:N112)/111

Format N113 as a percentage.