Quick answer
The MONTH formula returns the month number from a date.
=MONTH(A2)Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=MONTH(serial_number)When to use it
Date and time formulas are useful for schedules, invoices, project trackers, HR records, subscription reports, and monthly dashboards. They help convert calendar logic into repeatable calculations.
- Calculate days between two dates
- Find the end of a month
- Count working days excluding weekends
- Create due dates and renewal dates
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Start date or source date |
| B | End date, months, or working days |
| C | Date or duration result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic MONTH example
=MONTH(A2)Confirm Excel recognizes the source value as a real date or time.
Return blank for missing dates
=IF(A2="","",MONTH(A2))Prevents unfinished rows from showing default dates or errors.
Build a due-date rule
=IF(TODAY()>A2,"Overdue","On time")Useful for invoices, tasks, renewals, and project trackers.
Calculate by month
=EOMONTH(A2,0)Group activity by month-end dates for reporting.
Exclude holidays
=NETWORKDAYS(A2,B2,$H$2:$H$20)Use a separate holiday list for business-day calculations.
Create a report label
=TEXT(A2,"mmm yyyy")Convert dates into clean month labels for dashboards.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- The date is stored as text instead of a real Excel date.
- The result is correct but the cell is formatted as a number or date incorrectly.
- Holiday ranges are not locked with dollar signs before copying down.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the MONTH formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.