Repairing an Inward Dak Register and Its Procedure
A clerk in the station orderly room maintains the inward dak register in a spreadsheet with 240 rows and the columns: Sl No, Date received, From, Subject, Marked to, Date returned, and Days pending. Three problems have surfaced before the monthly review. About thirty dates in the 'Date received' and 'Date returned' columns were typed as text, and the 'Days pending' formula in those rows returns an error. One letter has been entered twice under different serial numbers because it was received once through the registry and once by hand. The file named 'Dak_register_final.xlsx' on the shared drive was overwritten by a colleague's older copy, and there is no other copy. In the same lot there is one letter marked 'Confidential' whose subject line has been typed in full in the open register. The adjutant wants the register corrected and a short written procedure so that the same problems do not recur next month.
1. Write a formula for the 'Days pending' column that copes with letters not yet returned and with text-stored dates, and explain how to convert the text dates in bulk.
Read the worked answer
A formula that tests the returned date first and traps the error, in cell G2:
=IF(F2="","Pending",IFERROR(F2-B2,"Check date"))
If a running figure is wanted even before return, use =IFERROR(IF(F2="",TODAY(),F2)-B2,"Check date").
To find the offending rows, put =ISNUMBER(B2) in a helper column: FALSE identifies a date stored as text (typically typed with a leading apostrophe or in a mixed format), which cannot be subtracted. To fix in bulk rather than row by row, select the column and use Data → Text to Columns → Finish, or use =DATEVALUE(B2) in a helper column and paste the values back over the original, then format the column as a date. Also scan for two-digit years read as 1925 instead of 2025, which give a huge positive 'Days pending' and will not raise an error.
2. How would you confirm and remove the duplicate entry without destroying a genuine record, and how would you reconcile the register afterwards?
Read the worked answer
Confirm before deleting. Two genuinely different letters can share a subject line - for example two separate inspection programmes - so compare From, Subject, Date received and Marked to together. Either filter those columns and read the pairs, or use =COUNTIFS($C$2:$C$241,C2,$D$2:$D$241,D2,$B$2:$B$241,B2) and look for counts greater than 1.
Remove. Keep the row carrying the registry stamp and the earlier serial number, delete the other, and record the deleted serial in a remarks column so that the gap is explained at audit rather than appearing as a missing entry.
Reconcile. The row count should fall from 240 to 239; check that figure against the physical pages of the dak register, re-run the duplicate check to confirm none remain, and then apply the corrected 'Days pending' formula to every row so the monthly pending list is computed on clean data.
3. Set out the correct handling of the confidential letter and the backup procedure the adjutant should circulate.
Read the worked answer
Confidential letter. It should not have been entered in the open register with its subject, and it must not be scanned, e-mailed or left in a shared folder. Correct handling: enter it in the confidential dak register by serial number only; keep the sealed cover unopened; hand it to the officer authorised to receive confidential mail and obtain a signed acknowledgment with date and time. In the open register, strike out the subject already typed and write 'Confidential' in its place, and report the exposure to the adjutant rather than quietly editing the sheet - a quietly corrected register conceals a security lapse.
Backup procedure. Put the date in the filename (Dak_register_2025-08-05.xlsx) and never use 'final'; keep the live file on the restricted station server, not in a personal folder; take a dated weekly copy and one before any bulk edit; enable version history so an overwrite can be rolled back. Where version history is unavailable, ask the EDP section whether the server keeps previous versions, and reconstruct the lost week from the physical register before it is archived.