cansevin
asked on
Day of year in excel
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!
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!
So format column B as Number (no decimals) , put the formula in B1, and copy down.
Hi
And if by any chance you need the number of days remaining in the year:
=DATE(YEAR(A1),12,31)-A1
And if by any chance you need the number of days remaining in the year:
=DATE(YEAR(A1),12,31)-A1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 formulaNo need to do that if the destination cell is defined as a Number with no decimals as I suggested above.
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
....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
I stand corrected. INT should be used.
ASKER
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!
@cansevin: Did you consider giving me part of the points?
=A1-DATE(YEAR(A1),1,0)