Day, Month, Year, Weekday from date in Excel

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)
Excel How To - Month from date. Year from date
Excel How To – Month from date. Year from 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)
Excel How To - Month from date. Week from date
Excel How To – Month from date. Week from date

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:

excel_how_to_dateparts_from_date.xlsm

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: