Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel INDIRECT Function
  • How to get first column number in range in Excel
  • Count rows with at least n matching values
  • How to get last row in text data in Excel
  • How to create dynamic named range with OFFSET in Excel

Data Analysis Examples

  • How to create dynamic reference table name in Excel
  • How to create a Histogram in Excel
  • Chart Axes in Excel
  • Create Scatter Chart in Excel
  • How To Create Pareto Chart in Excel

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation don’t exceed total
  • Excel Data validation date in specific year
  • How To Create Drop-down List in Excel
  • Excel Data validation exists in list

How to calculate nth day of year in Excel

by

To get the nth day of year based on a given date, you can use a formula based on the DATE and YEAR functions.

Formula

=date-DATE(YEAR(date),1,0)

Explanation

In the example shown, the formula in C5 is:

=B4-DATE(YEAR(B4),1,0)

With the date “June 1, 2016” in cell B4, the formula returns 153, since June 1st is the 153rd day of that year.

Worked Example:   How to generate random date between two dates in Excel

How the formula works

This formula takes advantage of the fact that dates are just sequential numbers in Excel. It determines the last day of the previous year and subtracts that value from the original date B4. The result is nth day of the year.

Worked Example:   How to generate series of dates increment by 1 year from a single start date in Excel

Note the day argument in DATE is supplied as zero. A nice feature of DATE function is it can handle DAY values that are “out of range” and adjust the result appropriately. When we give DATE a year, a 1 for month, and zero for DAY, the DATE function returns the last day of the previous year.

Worked Example:   Add years to date in Excel

Day of year as of today

To adjust the formula to return the nth day of year for the current date, just use the TODAY function for the date:

=TODAY()-DATE(YEAR(TODAY()),1,0)

Post navigation

Previous Post:

How to calculate project start date based on end date in Excel

Next Post:

How to calculate percent of year complete 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
  • REPT function: Description, Usage, Syntax, Examples and Explanation
  • Excel MAX, MATCH and ADDRESS function illustration
  • How to get last word in a cell in Excel
  • Get last name from name with comma — Manipulating NAMES in Excel
  • Split numbers from units of measure in Excel
  • How to calculate Next working/business day in Excel
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • EOMONTH function: Description, Usage, Syntax, Examples and Explanation
  • Display Days in month in Excel
  • How to calculate project start date based on end date in Excel
  • TBILLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • AMORLINC function: Description, Usage, Syntax, Examples and Explanation
  • ODDFPRICE function: Description, Usage, Syntax, Examples and Explanation
  • DDB function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles