Dates in Excel are just numbers (1 January 1900 is day 1), which is why they can be added and subtracted. Assume the date is in A2.
In this article
- Month and year
- Quarters and financial years
- Weeks and days
- Differences and working days
- Fixing text that looks like dates
Month and year
| Result |
Formula |
| First day of the month |
=EOMONTH(A2,-1)+1 |
| Last day of the month |
=EOMONTH(A2,0) |
| Days in the month |
=DAY(EOMONTH(A2,0)) |
| Same day next month |
=EDATE(A2,1) |
| Month name |
=TEXT(A2,"mmmm") |
| “2025-03” label |
=TEXT(A2,"yyyy-mm") |
| First day of the year |
=DATE(YEAR(A2),1,1) |
Quarters and financial years
| Result |
Formula |
| Calendar quarter |
="Q"&ROUNDUP(MONTH(A2)/3,0) |
| Indian FY (Apr–Mar) label |
="FY"&TEXT(EDATE(A2,-3),"yy")&"-"&TEXT(EDATE(A2,9),"yy") |
| FY quarter (Apr = Q1) |
="Q"&ROUNDUP(MONTH(EDATE(A2,-3))/3,0) |
Weeks and days
| Result |
Formula |
| Day name |
=TEXT(A2,"dddd") |
| Monday of that week |
=A2-WEEKDAY(A2,3) |
| ISO week number |
=ISOWEEKNUM(A2) |
| Is it a weekend? |
=WEEKDAY(A2,2)>5 |
Differences and working days
| Result |
Formula |
| Days between |
=B2-A2 |
| Complete months |
=DATEDIF(A2,B2,"m") |
| Age in years |
=DATEDIF(A2,TODAY(),"y") |
| Working days |
=NETWORKDAYS(A2,B2,Holidays) |
| Working days, Sunday-only weekend |
=NETWORKDAYS.INTL(A2,B2,11) |
| Due date in 10 working days |
=WORKDAY(A2,10,Holidays) |
Fixing text that looks like dates
=DATEVALUE(A2) converts text in your system’s format.- For “15.03.2025” from SAP:
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)).
- Whole column: Data → Text to Columns → Finish, choosing DMY in step 3.
Try these live in the Formula Lab date tab or the Date & Workday Calculator.