Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Get column index in Excel Table
  • Use Data Form to input, edit and delete records in Excel
  • Excel Frequency Function Example
  • Excel Line Chart
  • Working With Tables in Excel

References

  • How to create dynamic named range with OFFSET in Excel
  • How to reference named range different sheet in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to get last row in numeric data in Excel
  • How to get last row in mixed data with blanks in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation number multiple 100
  • Excel Data validation whole percentage only
  • Excel Data validation exists in list
  • Prevent invalid data entering in specific cells

Get week number from date in Excel

by

If you need to get the week number from the day from a date, you can use the WEEKNUM function. The date must be in a form that Excel recognizes as a valid date.

Note: The WEEKNUM function takes two arguments, a date, and, optionally, an argument called return_type, which controls the scheme used to calculate the week number.

Formula

=WEEKNUM(date)

Explanation

In the example, the formula is:

=WEEKNUM(B5)

B4 contains a date value for January 5, 2016. The DAY function returns the number 2 representing the calculated week number.

Note that by default, the WEEKNUM function uses a scheme where week 1 begins on January 1, and week 2 begins on the next Sunday (when the return_type argument is omitted, or supplied as 1).

With a return_type of 2, week 1 begins on January 1, and week 2 begins on the next Monday.

ISO week number

With ISO week numbers, week 1 starts on the Monday of the first week in a year with a Thursday. This means that the first day of the year for ISO weeks is always a Monday in the period between Jan 29 and Jan 4.

Starting with Excel 2010 for Windows and Excel 2011 for Mac, you can generate an ISO week number using 21 as the return_type:

=WEEKNUM(date,21)

Post navigation

Previous Post:

Get day from date in Excel

Next Post:

Calculate loan interest in given year in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • Check multiple cells are equal in Excel
  • How to use Excel XOR Function

Date Time

  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Get project end date in Excel
  • Convert date to text in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • Add days to date in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • Group numbers at uneven intervals in Excel
  • Group times into unequal buckets in Excel
  • Map inputs to arbitrary values in Excel

General

  • Subtotal by invoice number in Excel
  • Hide and Unhide Columns or Rows in Excel
  • How to generate random date between two dates in Excel
  • Zoom Worksheet in Excel
  • How to Delete Cells, Row and Rows in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning