Last Updated on January 11, 2023 by token
1. How to extract day, month, year, weekday and quarter from date in Excel?
If you are reading this article, you would probably like to use the date and time function in Excel. Excel has built-in sheet functions to help you calculate and extract date parts from the date. At the beginning, you should make sure that our date is really recognized in Excel as date.To work on real data, let’s add some sample dates to our spreadsheet by copying them to the spreadsheet:
2017-01-01
1985-10-19
2017-05-03
2000-11-11
2004-06-19
2019-12-31
To calculate our title data, you can use the date functions in Excel. These are:
- day of month from day: =DAY(date)
- day of week from date: =WEEKDAY(date)
- month from date: =MONTH(date)
- quarter from date: =ROUNDUP(MONTH(date)/3,0)
- year from date: =YEAR(date)
- week of year from date: =WEEKNUM(date)
As you can see, we calculated the quarter in Excel using formulas that indicate the month and round function.
2. Monday as first day of week in Excel
Functions WEEKDAY and WEEKNUM have second argument. This argument specifies how the days of the week are assigned. For week numbering:
- from sunday to saturday, insert number 1 or leave this argument blank
- from monday to sunday, use number 2
For example, if you want to use WEEKDAY and WEEKNUM functions from monday to sunday type formulas using number 2 in second argument:
- day of week: =WEEKDAY(date, 2)
- week of year: =WEEKNUM(date, 2)
If you are looking for similar examples in Excel, I invite you to visit this section: Excel How To. Excel file with the examples is attached below: