Organize information

Generate an Excel formula from a requirement

Turn a calculation need and real column layout into a formula you can verify

4 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Calculation requirementColumns or cellsExample rows and expected results

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Create an Excel formula for this requirement:
[requirement]

Workbook layout:
[layout]

Example rows and expected results:
[examples]

Use the stated headers or cell references exactly. Do not invent missing columns. If the requirement is ambiguous, list the ambiguity before proposing a formula. Return:
1. Formula for the first data row
2. Plain-language explanation of each part
3. Assumptions about blanks, errors, dates, text, and regional separators
4. Three test cases with expected results
5. A simpler alternative if one exists

Prefer standard Excel functions and do not use volatile functions unless they are necessary.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Calculation requirement: In column E, calculate the number of calendar days an order was open. Use the closed date when present; otherwise use 30 June 2026. Leave the result blank when the created date is missing.
Columns or cells: A: Order ID (text); C: Created Date (Excel date); D: Closed Date (Excel date or blank); E: Days Open. Data starts on row 2.
Example rows and expected results: C2=1 Jun 2026, D2=5 Jun 2026 => 4; C3=20 Jun 2026, D3 blank => 10; C4 blank, D4 blank => blank.

Example output

Formula for E2 =IF(C2="","",IF(D2="",DATE(2026,6,30)-C2,D2-C2))
How it works The outer IF returns an empty string when C2 has no created date. The inner IF uses 30 June 2026 when D2 is blank; otherwise it subtracts the created date from the closed date. Excel stores dates as serial numbers, so subtraction returns calendar days.
Assumptions C and D contain real Excel dates, not text. A same-day close returns 0. A closed date before creation produces a negative number and should be investigated rather than hidden. The formula uses commas; installations using semicolons must replace the separators.
Tests 1 Jun to 5 Jun → 4; 20 Jun with no close → 10; missing created date → blank. Add a same-day case to confirm that 0 is intended.
Alternative There is no meaningfully simpler standard formula that preserves all three rules.

Why this works

1
Real headers and expected results constrain the formula to the actual workbook.
2
Explicit edge cases expose blank and type-handling mistakes before the formula is filled down.