Compliance Tracker

[colpie]

Hi all

I have a tracker built for compliance that tracks due dates for certificates. Included are formats to colour code when something is overdue (Red) and due soon (Yellow).

I would like to add an automated percentage complete element to this, at the moment I have a manual tick box that calculates this but would like the ticked boxes to untick when overdue.

Can anyone make any recommendations?

Thanks in advance.

 

[sxhall]

Hi Colpie,

Do you need the tick box? Could do this with a formula and the 'Windings' font based on the date....

Below formula with Wingdings font applied to the cell will look at the dates and add a tick, or not, when something is overdue.

=IF(B2<TODAY(),"","ü")

Entered in to range G2:M4 in the above image

Add some conditonal formatting and your done. Can still use the result to calculate the percentage as well.

=SUM(COUNTIF(H2:M2,"ü")/6)

Above in column N on my picture

The formula will make it a lot simpler to add/remove/expand the table as well in the future.

 

[colpie]

Hi Colpie,

Do you need the tick box? Could do this with a formula and the 'Windings' font based on the date....

View attachment 119056

Below formula with Wingdings font applied to the cell will look at the dates and add a tick, or not, when something is overdue.

=IF(B2<TODAY(),"","ü")

Entered in to range G2:M4 in the above image

Add some conditonal formatting and your done. Can still use the result to calculate the percentage as well.

=SUM(COUNTIF(H2:M2,"ü")/6)

Above in column N on my picture

The formula will make it a lot simpler to add/remove/expand the table as well in the future.

Hi sxhall

Thanks for the reply.

The tick box isn't essential, I added that as a manual way to visually display the unit %. As long as I can show % complete at the end of each row and in total for the whole estate it would work for me.

Thanks