Ravindra BagaleCourses & study guides मराठी Track your progress

Milk Bill Calculator in Excel: Daily Litres Calendar, Monthly Bill and Missed Days

By Last updated:

On the 1st of every month the milkman in Pune tells Shraddha Bagale the bill, and every month there is the same small argument. "We were out of town for three days." "But you took extra on Sundays." A one-page calendar in Excel ends it: type the litres each day, and the total, missed days and extra days are counted for you.

What you'll learn on this page

  • A date column that fills itself for the month
  • The weekday name with TEXT
  • Amount per day and the monthly bill
  • Missed days and extra days with COUNTIF
  • Sunday litres with SUMIF
  • Advance paid and balance to pay

Download the template: milk-bill-calculator-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

October 2026 at ₹56 a litre (made-up rate). The usual order is 1 litre a day, 1.5 litres on Sundays, nothing on 6 to 8 October (out of town) and 2 litres on 20 and 21 October (guests).

Value
Total litres 32.0
Bill ₹1,792
Days with no delivery 3
Days with extra milk 6 (four Sundays + two guest days)
Sunday litres 6.0
Bill if every day were usual ₹1,736
Advance paid ₹500
Balance to pay ₹1,292

Both sides can see that the three missed days saved ₹168, but the extra days added ₹224, so the bill is ₹56 more than a usual month.

Inputs (yellow) Rate ₹56 a litre First date of month Litres each day Per day Date = A7 + 1 Day = TEXT(A7,"ddd") Amount = litres × rate Summary 32 litres, ₹1,792 3 missed, 6 extra Balance ₹1,292

Figure 1. One row per day: litres × rate gives the day's amount; SUM gives the ₹1,792 bill; COUNTIF counts 3 missed and 6 extra days; the advance leaves ₹1,292 to pay.

The milk-bill-calculator-template.xlsx template sheet with example data, yellow input cells and green formula cells

Figure 2. The template sheet, recalculated with its example data. Download it above and replace the yellow cells with your own numbers.

2. Build it step by step

Step 1. Rate and usual litres

  1. In B3 type the rate per litre (₹56) and in B4 the usual litres per day (1).

Step 2. Dates that fill themselves

  1. In A6:E6 type Date, Day, Litres, Amount, Note.
  2. In A7 type 01-10-2026.
  3. In A8 type =A7+1 and copy down to A37 (31 days). Next month you change only A7 (dates).
  4. In B7 type =TEXT(A7,"ddd") and copy down: Thu, Fri, Sat … (TEXT).

Step 3. Litres and amount

  1. Type the litres taken each day in C7:C37 (yellow). Type 0 when no milk came.
  2. In D7 type =C7*$B$3 and copy down.
  3. Select E7:E37 > Data > Data Validation > List: Out of town,Guests,Festival,Milkman on leave (drop-down lists).

Step 4. Totals

  1. In C38 type =SUM(C7:C37): 32 litres.
  2. In D38 type =SUM(D7:D37): ₹1,792.

Step 5. Missed, extra and Sunday

  1. In H7 type =COUNTIF(C7:C37,0): 3 days with no delivery.
  2. In H8 type =COUNTIF(C7:C37,">"&B4): 6 days with more than the usual litre (COUNTIF).
  3. In H9 type =B4*COUNT(C7:C37)*B3: ₹1,736, the bill for a usual month.
  4. In H10 type =SUMIF(B7:B37,"Sun",C7:C37): 6 litres on Sundays.

Step 6. Advance and balance

  1. Type the advance paid in H11 (₹500).
  2. In H12 type =D38-H11: ₹1,292 to pay.

Step 7. Colours

  1. Select A7:E37 > Conditional Formatting > New Rule > Use a formula: =$C7=0, light red (formula rules).
  2. Add =$C7>$B$4 with light green for extra days.

Ravindra Bagale's Tip

Fill the litres every night, or at least every Sunday. Remembering 31 days at month-end is exactly where the arguments start.

3. Formulas used

Cell Formula What it does
A8 =A7+1 Next date (copied down the month)
B7 =TEXT(A7,"ddd") Weekday name
D7 =C7*$B$3 Amount for the day
C38 =SUM(C7:C37) Total litres
D38 =SUM(D7:D37) Monthly bill
H7 =COUNTIF(C7:C37,0) Days with no delivery
H8 =COUNTIF(C7:C37,">"&B4) Days with more than the usual litres
H9 =B4*COUNT(C7:C37)*B3 Bill for a usual month
H10 =SUMIF(B7:B37,"Sun",C7:C37) Litres taken on Sundays
H12 =D38-H11 Balance to pay

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

  1. Leaving a missed day blank. A blank is not counted by COUNTIF(...,0). Type 0 for days with no milk.
  2. Typing the rate in every row. Keep the rate once in B3 and use $B$3. When the rate changes, you change one cell.
  3. Typing every date by hand. Type only the 1st and let =A7+1 fill the rest, so no date is skipped or repeated.
  4. "Sun" spelt differently. SUMIF looks for the exact text the TEXT formula makes. Type "Sun", not "Sunday".
  5. Using the 31-row sheet for a 30-day month. Clear row 37 in a 30-day month, or the 1st of the next month is billed.

Ravindra Bagale's Tip

Show the sheet on your phone when you pay. Missed and extra days are coloured, so the bill is clear in a few seconds.

5. More real examples

Example 1: Newspaper bill. Same sheet: copies per day in column C and the price per copy in B3. Sunday papers often cost more, so add a Sunday price with =IF(B7="Sun",price2,price1).

Example 2: Two milk types. Add columns for cow and buffalo milk with their own rates, and add both amounts in the total.

Example 3: Rate change mid-month. Add a Rate column, type the new rate from the change date, and use =C7*F7 instead of $B$3.

Quick revision cards

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

Revision card 1 for Milk Bill Calculator in Excel: Weekday name: =TEXT(A7,"ddd") gives Thu; Next date (copied down the month): =A7+1 gives 02-10-2026; Amount for the day: =C7*$B$3 gives ₹56. Includes an example data table.
Card 1 of 2: Milk Bill Calculator in Excel · Download PNG
Revision card 2 for Milk Bill Calculator in Excel: Total litres: =SUM(C7:C37) gives 32.0; Monthly bill: =SUM(D7:D37) gives ₹1,792. Includes an example data table.
Card 2 of 2: Milk Bill Calculator in Excel · Download PNG

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate a monthly milk bill in Excel?

Type the litres for each date, multiply by the rate (=C7*$B$3) and SUM the amounts. This month: 32 litres × ₹56 = ₹1,792.

How do I count the days milk was not delivered?

=COUNTIF(C7:C37,0) counts the days with 0 litres.

How do I show the day name next to a date?

=TEXT(A7,"ddd") gives Mon, Tue and so on; "dddd" gives the full name.

How do I add only Sunday litres?

=SUMIF(B7:B37,"Sun",C7:C37) adds the litres where the day is Sun.

Can I reuse the sheet next month?

Yes. Copy the sheet, change A7 to the 1st of the new month and type the new litres.

7. Quick quiz

Ten questions on this page. Sign in to save your score.

Loading practice tasks...

  1. Total clicks (Easy): Add up Clicks in D2:D11. (sheet "Data")
  2. Average spend (Easy): Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Google (Easy): How many rows have Platform "Google"? (sheet "Data")
  4. Largest clicks (Easy): The largest value in D2:D11. (sheet "Data")
  5. Days taken (Easy): C2 minus B2. (sheet "Dates")
  6. Start year (Easy): Year of B3. (sheet "Dates")
  7. Due month (Easy): Month number of C3. (sheet "Dates")
  8. Earliest start (Easy): Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Clicks for Google (Medium): Total of D2:D11 where B is "Google". (sheet "Data")
  10. Google in Paris (Medium): Total of D2:D11 where B is "Google" and C is "Paris". (sheet "Data")
  11. Working days (Medium): Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due (Medium): Weekday number of C4 (Sunday = 1). (sheet "Dates")

See all practice tasks in the Task Hub →