Build my office setupOffice setup

Brightwork demonstration · 07 / 11 · Code and IT help / A broken script, a formula

CODE CHECK

A broken script and a spreadsheet request. CODE CHECK is an original Brightwork demonstration. A local model fixes an office script that failed and turns a plain-words request into spreadsheet formulas. Both answers were then run: the script on copies of the scanner folder, the formulas in LibreOffice Calc.

4 documentsOutputs by Qwen3.8-27B, generated locallyShown uneditedChecked by Brightwork: 1 correction across 2 outputs
CC0 · The script and the errorFictional source
"""Rename scanner output so files sort by date.

Scan_16-10-2026_0932.pdf  ->  2026-10-16 0932 scan.pdf

Fictional script written for a Brightwork demonstration. Usage: python rename_scans.py FOLDER
"""
import os
import sys
from datetime import datetime

folder = sys.argv[1]
for name in os.listdir(folder):
    stem = name[:-4]
    _, date_part, time_part = stem.split('_')
    when = datetime.strptime(date_part + time_part, '%m-%d-%Y%H%M')
    new = when.strftime('%Y-%m-%d %H%M') + ' scan.pdf'
    os.rename(os.path.join(folder, name), os.path.join(folder, new))
    print(name, '->', new)

What Grace saw

From: Grace Okafor

To: Lodestar IT

The scan-renaming script stopped halfway through this morning. This is what it printed. The scanner folder has this week's scans in it, plus the scanner's own settings file. Can you fix it?

C:\Office\Scripts> python rename_scans.py "S:\Scans\Inbox"
Scan_02-10-2026_1015.pdf -> 2026-02-10 1015 scan.pdf
Scan_09-10-2026_1452.pdf -> 2026-09-10 1452 scan.pdf
Traceback (most recent call last):
  File "C:\Office\Scripts\rename_scans.py", line 15, in <module>
    when = datetime.strptime(date_part + time_part, '%m-%d-%Y%H%M')
  File "C:\Program Files\Python312\Lib\_strptime.py", line 554, in _strptime_datetime
    tt, fraction, gmtoff_fraction = _strptime(data_string, format)
  File "C:\Program Files\Python312\Lib\_strptime.py", line 333, in _strptime
    raise ValueError("time data %r does not match format %r" %
ValueError: time data '14-10-20261630' does not match format '%m-%d-%Y%H%M'

Files still in the folder:

2026-02-10 1015 scan.pdf
2026-09-10 1452 scan.pdf
Scan_14-10-2026_1630.pdf
Scan_16-10-2026_0932.pdf
Scan_16-10-2026_0932(1).pdf
scanner-settings.ini

The scanner names every file Scan_DD-MM-YYYY_HHMM.pdf. When two pages are scanned in the same minute it adds (1), (2) and so on.

CC1 · The fix and what went wrongQwen3.8-27B
Tested by running · 17 October 2026, Python 3, on copies of the folder
  • On Grace’s folder as it stands, it renamed the three remaining scans to 2026-10-14 1630, 2026-10-16 0932 and 2026-10-16 0932(1), and left the two misnamed files and the settings file alone. Each file kept its own contents.
  • When the new name was already taken, it saved the scan as “2026-10-16 0932 scan (1).pdf” instead of overwriting.
  • Run a second time, it changed nothing.
Checked by Brightwork · 1 correction
  • It says the old script would have made the two same-minute scans clash. In fact the old script crashes on the “(1)” file (“unconverted data remains”). The fixed script handles both, as tested.

What went wrong

The old script had the date order backwards.

The scanner names files like this:

Scan_DD-MM-YYYY_HHMM.pdf

So Scan_16-10-2026_0932.pdf means 16 October 2026 at 09:32.

The old script treated the date as month-day-year.

That is why it produced wrong dates for the first two files.

Then it stopped at Scan_14-10-2026_1630.pdf because it tried to use 14 as a month. There is no 14th month.

What the error does not show

Two files have already been renamed wrongly

The error log shows these lines:

Scan_02-10-2026_1015.pdf -> 2026-02-10 1015 scan.pdf

Scan_09-10-2026_1452.pdf -> 2026-09-10 1452 scan.pdf

Those new names are wrong.

The correct names should be:

  • 2026-10-02 1015 scan.pdf
  • 2026-10-09 1452 scan.pdf

So the two files currently in the folder that are named:

  • 2026-02-10 1015 scan.pdf
  • 2026-09-10 1452 scan.pdf

need to be renamed manually.

If Windows says a file with the new name already exists, stop and check manually. Do not let it replace the file.

The script stopped early

The folder still contains:

  • Scan_14-10-2026_1630.pdf
  • Scan_16-10-2026_0932.pdf
  • Scan_16-10-2026_0932(1).pdf
  • scanner-settings.ini

The old script did not finish the job.

The scanner settings file will also stop it

The old script expects every file to be a scan file with the scanner name pattern.

It will not handle scanner-settings.ini safely.

Same-minute duplicates will cause a name clash

The scanner adds (1), (2) and so on when two pages are scanned in the same minute.

The old script did not keep that marker.

Both of these files would try to become the same name:

  • Scan_16-10-2026_0932.pdf
  • Scan_16-10-2026_0932(1).pdf

The fixed script keeps the duplicate marker.

What to do now

  1. Do not run the old script again.
  2. Manually rename the two files that were already renamed wrongly:
    • 2026-02-10 1015 scan.pdf to 2026-10-02 1015 scan.pdf
    • 2026-09-10 1452 scan.pdf to 2026-10-09 1452 scan.pdf
  3. Replace the old script with the fixed script below.
  4. Run the fixed script on the scan folder.
  5. Check the printed lines before and after the run.

For the folder you showed, the fixed script should:

  • leave the two already renamed files alone
  • rename Scan_14-10-2026_1630.pdf
  • rename Scan_16-10-2026_0932.pdf
  • rename Scan_16-10-2026_0932(1).pdf
  • leave scanner-settings.ini alone

It should not stop just because one file is not a scan file.

Fixed script

"""Rename scanner output so files sort by date.

Expected scanner names:
    Scan_16-10-2026_0932.pdf
    Scan_16-10-2026_0932(1).pdf

Renamed to:
    2026-10-16 0932 scan.pdf
    2026-10-16 0932 scan(1).pdf

Usage:
    python rename_scans.py FOLDER
"""

import os
import re
import sys
from datetime import datetime

PATTERN = re.compile(
    r"^Scan_(\d{2})-(\d{2})-(\d{4})_(\d{2})(\d{2})(?:\((\d+)\))?\.pdf$"
)


def make_unique_path(path, source_path):
    """Return a path that does not already exist.

    This stops the script from overwriting an existing file.
    """
    if not os.path.exists(path) or os.path.abspath(path) == os.path.abspath(source_path):
        return path

    root, ext = os.path.splitext(path)
    number = 1

    while True:
        candidate = f"{root} ({number}){ext}"
        if (
            not os.path.exists(candidate)
            or os.path.abspath(candidate) == os.path.abspath(source_path)
        ):
            return candidate
        number += 1


def main():
    if len(sys.argv) != 2:
        print("Usage: python rename_scans.py FOLDER")
        return 1

    folder = sys.argv[1]

    if not os.path.isdir(folder):
        print(f"Folder not found: {folder}")
        return 1

    renamed = 0
    skipped = 0

    for name in sorted(os.listdir(folder)):
        match = PATTERN.match(name)

        if not match:
            print(f"Skipped (not a scanner PDF): {name}")
            skipped += 1
            continue

        day = int(match.group(1))
        month = int(match.group(2))
        year = int(match.group(3))
        hour = int(match.group(4))
        minute = int(match.group(5))
        duplicate = match.group(6)

        try:
            when = datetime(year, month, day, hour, minute)
        except ValueError as exc:
            print(f"Skipped (invalid date or time): {name}: {exc}")
            skipped += 1
            continue

        base_name = when.strftime("%Y-%m-%d %H%M scan.pdf")

        if duplicate:
            target_name = base_name[:-4] + f"({duplicate}).pdf"
        else:
            target_name = base_name

        source_path = os.path.join(folder, name)
        target_path = make_unique_path(
            os.path.join(folder, target_name),
            source_path,
        )

        if os.path.abspath(target_path) == os.path.abspath(source_path):
            print(f"Skipped (already renamed): {name}")
            skipped += 1
            continue

        os.rename(source_path, target_path)
        renamed += 1
        print(f"{name} -> {os.path.basename(target_path)}")

    print(f"Done. Renamed {renamed}, skipped {skipped}.")
    return 0


if __name__ == "__main__":
    sys.exit(main())
CCF · The requestFictional source

A formula, in plain words

From: Tom Avery, Avery Joinery Pty Ltd

I keep my invoices in a spreadsheet. Row 1 has the headings and each invoice is on its own row from row 2 down:

  • Column A: invoice number
  • Column B: customer
  • Column C: invoice total, including GST
  • Column D: invoice date
  • Column G: I type "Paid" when the money comes in. Otherwise it's empty.

I want two new columns:

  • Column E: the amount before GST. GST is 10%.
  • Column F: the word "Overdue" if the invoice is more than 30 days old and not paid. Otherwise leave it blank.

I use Excel. Can you give me the formulas for row 2, so I can copy them down?

CC2 · The formulasQwen3.8-27B
Tested by running · 17 October 2026, LibreOffice Calc 26.8 (headless), the formulas exactly as written, copied down nine test invoices
  • Amounts before GST came out right on all nine rows, for example $1,628.44 to $1,480.40.
  • “Overdue” appeared on the invoices 31, 45 and 60 days old and unpaid; not on one exactly 30 days old, one paid, one 10 days old or one with no date.
  • “paid” in lower case counted as paid; “ Paid ” with spaces did not, as the answer warns.
Checked by BrightworkNo corrections. Checked against the source.

Formulas for row 2

Column E, cell E2

=C2/1.1

Column F, cell F2

=IF(AND(ISNUMBER(D2),TODAY()-D2>30,G2<>"Paid"),"Overdue","")

Plain-word explanation

Column E

  • C2 is the invoice total, including GST.
  • GST is 10%, so dividing by 1.1 removes the GST.
  • E2 shows the amount before GST.

Column F

The formula checks three things:

  • D2 contains a real Excel date.
  • The invoice date is more than 30 days before today.
  • G2 does not say “Paid”.

If all three are true, F2 shows Overdue. Otherwise, F2 stays blank.

Copy down

  • Put the first formula in E2.
  • Put the second formula in F2.
  • Copy E2 and F2 down through your invoice rows.

Things to watch out for

  • Column D must contain Excel dates. If a date is saved as text, the overdue formula will not mark it Overdue.
  • If D is blank, the formula leaves F blank.
  • If C is blank, E will show 0. If C contains text, E may show an error.
  • If G contains “Paid” with extra spaces before or after it, the formula may not treat it as paid.
  • The formula uses TODAY(), so the overdue status can change as time passes.
  • If you want column E to look like money, format it as currency.

Fictional demonstration, not client documents. Our workstation: one RTX 3090 with 24 GB, every model layer on the GPU. These are not performance figures for a Brightwork installation.