Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Day of year in excel

Posted on 2015-02-11
Medium Priority
80 Views
Column A has a list of dates in the format:  5/8/2014  11:23:29 AM.

I would like Column B to have which day of the year it is (1 through 365).

Any idea how to make this happen? THanks!
0
Question by:cansevin
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 49

Expert Comment

ID: 40604309
format the target cell as a Number and then assuming the date is in A1

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

LVL 49

Expert Comment

ID: 40604312
So format column B as Number (no decimals) , put the formula in B1, and copy down.
0

LVL 11

Expert Comment

ID: 40604355
Hi

And if by any chance you need the number of days remaining in the year:

=DATE(YEAR(A1),12,31)-A1
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 40604475
If your date column also contains times as you say....and you want an integer returned then you probably need to add an INT function to Martin's suggested formula, i.e.

=INT(A1)-DATE(YEAR(A1),1,0)

regards, barry
0

LVL 49

Expert Comment

ID: 40604491
If your date column also contains times as you say....and you want an integer returned then you probably need to add an INT function to Martin's suggested formula
No need to do that if the destination cell is defined as a Number with no decimals as I suggested above.
0

LVL 50

Expert Comment

ID: 40604700
Hey Martin!

....but using your method would mean that the underlying value would still include the decimals, so that might be an issue if used in further calculations.....but also even the display could be wrong, I think.

I'm assuming that if the date is 1st January then cansevin would want to display just 1, whatever the time, for the first day of the year, but if the time is after noon then the result, using your method, will be 2, e.g. if the A1 entry is something like:

1/1/2015  4:25:00 PM

regards, barry
0

LVL 49

Expert Comment

ID: 40604705
I stand corrected. INT should be used.
0

Author Closing Comment

ID: 40607127
Thanks guys! INT was the way to go. Without it and no decimals, it would round to the next day if the time was after noon. Thanks for the help!
0

LVL 49

Expert Comment

ID: 40607135
@cansevin: Did you consider giving me part of the points?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month9 days, 23 hours left to enroll