The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,978 other subscribers

Day of the Year in Microsoft Excel

Posted by jpluimers on 2023/01/25

Given cell A1 is a valid date, I wanted to know the day of that date in that year.

My solution is =A1-DATE(YEAR(A1)-1,12,13)

I disliked the solution in [Wayback/Archive] Day of the Year in Microsoft Excel and [Archive] Day of the Year in Excel (In Easy Steps) (excluded from the WayBack machine), as it is unclear where the + comes from in their solution =A1-DATE(YEAR(A1),1,1)+1

So, here goes my solution, with explanation:

  • =YEAR(A1) is the year of A1
  • =YEAR(A1)-1 is year before A1
  • =DATE(YEAR(A1)-1,12,13) is the last day of year before A1
  • =A1-DATE(YEAR(A1)-1,12,13) is the day of the year of A1

The last step works because subtracting two dates in Excel returns the number of days between those two dates (in a similar way, you can add a number to a date to get a new date number days in the future; similarly you can add time portions as fractions of a day).

The linked solution uses:

  • =YEAR(A1) is the year of A1
  • =DATE(YEAR(A1),1,1) is the first day of the year of A1
  • =DATE(YEAR(A1),1,1)-1 is the last day of the year before A1
  • =A1-(DATE(YEAR(A1),1,1)-1) is the day of the year of A1
  • =A1-DATE(YEAR(A1),1,1)+1 is a simplification of the day of the the year of A1

[Wayback/Archive] excel days from start of year – Google Search

–jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: