Percentage of cells containing a date

[ooom]

Hi.
i have a spreadsheet where within a range of cells i insert dates on a daily basis
i would like a formula which calculates the percentage of cells containing a date out of the range i specify.

Please Login or Register  to view this content.

i got this from another topic and can change the range to fit my needs and works when i type in "h" but i am unsure how to replace the "h" in the formula so it loks for a date instead..

EDIT..
i have got this far on my own, now i can get a count on the cells with a date inserted from todays date back, but still cant convert it to be a percentage of my range....

Please Login or Register  to view this content.

[Nisha Dhawan]

Hi,

Please upload a sample file by following the steps in attached file:-

[adhawan06]

Try the following function in B1 assuming you have value and date range in column A.

Just modifiedyour formula as you need the total for dividing the numbers
=100%-COUNTIFS(A1:A8,"<"&TODAY())/COUNTA(A1:A8)

[ooom]

I have entered the formula adhawan06 with my range from A1:A8, entering any date in the range gives me %100 regardless...i could be doing something incorrect, not sure..

i have attached to my original post a sample sheet...

[adhawan06]

Checked your sheet and you have not used What I have given you in the formula cell.

You were using your own function which you have given me

Please Login or Register  to view this content.

Whereas I have given you

Refer to the attachment

[ooom]

Yes i uploaded my file as a sample after the poster before you asked me to,
i re applied your function to the sheet i uploaded and got great results, thank you for your help....

[adhawan06]

Glad it worked,

Please take some few seconds to mark this thread as SOLVED and to say thanks, please click on *Add Reputation.

[FDibbins]

The file you uploaded didnt really help much at all, all it contained what where you wanted the answer, but contained no sample data at all

I dont see how you will be able to ID the last cell in your range, unless the dates are consecutive, and increase as you progress downwards?

If thats the case, then try this...

	A	B
5	4/1/2015	64%
6
7	4/3/2015
8
9
10	4/6/2015
11	4/7/2015
12
13	4/9/2015
14	4/10/2015
15	4/11/2015

B5=COUNT(A5:A15)/MATCH(MAX($A$5:$A$15),$A$5:$A$15,0)

[ooom]

Thank you FDibbins for your input

[FDibbins]

Happy to help, thanks for the feedback