Daily site progress in Excel: done against the plan you typed
A daily progress board is one row per task on this site. You type the task, the zone, the planned quantity, the done quantity, and the unit. Planned and done are numbers you typed. The gap is planned minus done, and it stays blank if either number is missing. The flag is a word: behind when done is less than planned, on when done is at or above planned. COUNTIFS counts the rows whose flag is behind. This board is not a diary. A diary is one line per task per day, and a task can have many lines. If you put the diary on this board, one task becomes many rows and you can no longer answer "where is this task" by reading a single done quantity. Keep the latest cumulative done here. Keep the daily notes in the log. The note on this row can say what changed today. It does not replace the number. The sheet does not know the programme, the drawing, or a contractual date. You type the planned quantity. You type the done quantity after you have seen the work, or after you have accepted a figure from someone who saw it. IF only compares those two numbers. It does not turn a percent into meters. The unit column is text you type so a reader does not treat meters as pieces. If you change the unit, recount the numbers in the new unit. Do not leave mixed units in one column of quantities. A blank planned or a blank done does not become on. Blank means a number is missing. The flag stays blank until both numbers are typed. Zero done against a planned quantity above zero is behind. That is a real gap, not a missing cell. Before you tell the next trade the area is clear, read three tasks you can see. Planned must be the quantity you meant. Done must match what is in place. The unit must match the number. Elyx can write the gap and the flag. You still decide what the crew does next. The trial lasts 7 days, with no card.
The Problem
The board fails when done is a sentence. "Mostly finished, waiting on the seal" cannot be subtracted from a planned quantity. Put the sentence in the note. Put a number in done. The flag reads the number. It also fails when each morning starts a new row for the same task. You then have five done figures and the latest one is whichever row is on top. One task, one row. You replace the done quantity in place. Yesterday's figure, if you need it, lives in the log. A third failure is mixing today's increment with the cumulative done. This board holds the cumulative quantity in place, not the amount added since yesterday. If you type only today's increment into Done, the flag will say behind even when the task is finished, because the cell no longer holds the total. If you need the increment, subtract yesterday's done, from the log, from today's done. Do not store the increment in the Done column. A fourth failure is using the flag as a safety clearance or a payment certificate. Behind means the number you typed as done is under the number you typed as planned. It does not say the work is safe, inspected, or billable. Those decisions stay outside the formula. The note can record them. The count of behind rows is not a claim about the contract.
Benefits
One row per task, edited in place when you update the quantity in place.
Behind means done is under planned. On means done has reached planned.
A missing number stays blank, so an unmeasured task is not marked on.
The gap is planned minus done, in the unit you typed.
The daily diary stays a log, so the board does not grow a row per morning.
Step-by-Step Tutorial
Lay out one row per task
Name the sheet Today. Headers in row 1: Task, Zone, Planned, Done, Unit, Gap, Flag, Note. Task is the name the crew already uses. Zone is the area on this site, in the words you use on the walk. Planned and Done are numbers you type. Unit is text, such as m, m2, or each. Gap and Flag are formulas. Note is text. One task occupies one row for as long as it is work you are measuring on this site. Add a row when a task joins the board. Do not add a row because a new day started. If the same task name exists in two zones, Zone must make the row unique, and you filter by zone before you read a local count. Two rows with the same task and the same zone will both be counted.
Sort by Zone, then Task, and look for duplicates before you rely on the behind count.
Type the planned quantity you are measuring against
Planned is the quantity you chose as the comparison, in the unit of that row. It might be the quantity on your own measure, or a figure you copied from a schedule you trust. The sheet does not fetch it. Leave Planned blank if you do not have a number you will stand behind. Do not type "all" or "100%". If you want a percent, the planned number is 1, or 100, and you use that same scale on every percent row. Write the scale in Unit. Do not mix a percent row and a meter row and then sort the gap as if the numbers meant the same thing. The gap is only meaningful inside one row, in that row's unit.
If the planned quantity changes because the scope changed, type the new number and say so in the note. The old plan is not kept on this row.
Type the cumulative done quantity
Done is the latest cumulative quantity in place, in the same unit as Planned. You type it after you have seen the work, or after you accept a count from someone who has. Leave it blank if you have not measured. Do not type today's increment alone. Do not type a percent in Done when Planned is in meters. Format Planned and Done as numbers. A quantity stored as text will not compare, and the flag will not match the word you expect. Zero is a number. Use zero when you have looked and nothing is in place. Use blank when you have not looked.
Write the time of the look in the note if the crew is still working. The number is what you saw then.
Show the gap only when both numbers exist
With Planned in C2 and Done in D2, the gap is =IF(OR(C2="",D2=""),"",C2-D2). A missing number stays blank. You do not show a gap of the whole plan when done has not been typed, and you do not show a negative plan when planned has not been typed. A positive gap means planned is still above done. Zero means they match. A negative gap means done is above planned. That can be a real over-measure or a typing error. Read it before you celebrate. Format Gap as a number, in the unit of the row. Do not format it as a date.
=IF(OR(C2="",D2=""),"",C2-D2)The gap is not a duration. If you also track dates, put them in other columns. Do not subtract a date from a quantity.
Flag behind or on
The flag is =IF(OR(C2="",D2=""),"",IF(D2<C2,"behind","on")). Both numbers must be present. Done under planned is behind. Done equal to planned, or above it, is on. The word on does not mean the task is accepted, snagged, or paid. It means the quantity comparison you asked for has been met. If you need a third word for "done but not accepted", put that in the note, or add a status column that this COUNTIFS does not read. Do not overload behind to mean both a quantity gap and a quality hold. The count would then mix two decisions.
=IF(OR(C2="",D2=""),"",IF(D2<C2,"behind","on"))Copy the formula down every task row. A typed word in the flag column will not follow the numbers when you update done.
Count the tasks that are behind
Behind, for this board, means the flag is the word behind. =COUNTIFS(G:G,"behind") is that count. A second count by zone, with the zone name in J2, is =COUNTIFS(B:B,J2,G:G,"behind"). Read the site total and the zone total as different questions. The site total includes every zone on the sheet. The zone total includes only the zone you typed in J2. Blank flags are in neither count. Tasks marked on are not in the behind count. Put both counts at the top and label them in words, including the zone name, so a visitor does not treat a zone count as the whole site.
=COUNTIFS(G:G,"behind")If the count looks wrong, filter Flag and look for a hand-typed "Behind" with a capital. The count reads the exact word the formula writes.
Update done in place, and keep the diary separate
When you measure again, replace Done on the same row. Do not insert a row for the new day. If you keep a diary, append a line there with the date, the task, and what changed. The board's Done cell becomes the new cumulative figure. When a task leaves the measure, because it is finished and you no longer want it on the morning count, cut the row to a sheet named Closed, or leave it marked on and filter it out of the view you read aloud. A finished task left as behind, because you forgot to type the last quantity, stays in the count. Closing the row without updating the number hides the mistake. Update the number first.
Agree who types Done before the end of the walk. Two people typing two quantities into one cell leaves only the last save.
Check three tasks before you publish the count
Pick three rows you can see from where you stand, or from a measure you trust. Planned must be the quantity you meant, in the unit written on the row. Done must match what is in place, as a cumulative quantity, not as today's increment. Gap must equal planned minus done when both are filled, and blank when either is empty. Flag must be behind when done is under planned, on when done has reached planned, and blank when a number is missing. If one row fails, do not send the behind count. Fix the cell. The count is the count of the words on this sheet. It is not a programme update, and it is not a payment figure. A diary can hold the days. Do not paste the diary's rows onto this board.
Say the unit when you read a gap aloud. A gap of 12 means nothing until the row says meters, square meters, or each.
Template Features
One cumulative done
Done is the latest quantity in place. You replace it. The diary, if you keep one, holds the days.
Gap only when both numbers exist
Planned minus done is blank if either cell is empty. A missing measure does not become a full gap or a full completion.
=IF(OR(C2="",D2=""),"",C2-D2)Behind or on
Behind means done is under planned. On means done has reached planned. The word is not an inspection result.
=IF(OR(C2="",D2=""),"",IF(D2<C2,"behind","on"))A site count and a zone count
COUNTIFS on the flag is the site. COUNTIFS with the zone is one area. Label both.
=COUNTIFS(B:B,J2,G:G,"behind")Unit in its own column
The unit is text you type. The formulas do not convert it. Read the gap with the unit.
Concrete Examples
A task still short of the quantity you typed
You planned 40 meters. You measured 25 meters in place. You want the board to say behind.
Planned: 40. Done: 25. Unit: m.
Result: Gap is 15. Flag is behind. The behind count includes the row. The 15 is meters, because that is the unit on the row. It is not a percent and it is not a day.
A task you have not measured today
Planned is filled. You have not typed done. You do not want the row marked on, and you do not want a gap equal to the whole plan.
Planned: 40. Done: empty. Unit: m.
Result: Gap is blank. Flag is blank. The behind count does not include the row. Type done when you have a quantity. Until then the board shows the hole instead of inventing a result.
A task that has reached the plan
You measured 40 meters against a plan of 40. Yesterday the row was behind.
You replace Done with 40 on the same row. Planned stays 40.
Result: Gap is 0. Flag is on. The behind count drops by one. You did not add a new row for the day. The note can say what was completed on the walk.
Pro Tips
Zone plus task is the key
The same task name in two areas is two rows. If you look a task up, look it up with the zone, not with the name alone.
Do not convert units in your head at the gap
If the plan is in meters and the crew reports pieces, convert before you type, and write the unit you actually stored. The subtraction will not convert for you.
A percent row uses one scale
If Planned is 1 for complete, Done is a fraction on that scale. If Planned is 100, Done is on that scale. Do not mix them, and do not feed that gap into a meter total.
Blockers with an owner live on another list
A quantity gap is this board. A blockage that needs an owner and an age is an exception log. The note can mention the blockage. It does not age it.