Solved

Day of year in excel

Posted on 2015-02-11
9
67 Views
Last Modified: 2015-02-12
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
Comment
Question by:cansevin
9 Comments
 
LVL 46

Expert Comment

by:Martin Liss
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 46

Expert Comment

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

Expert Comment

by:Wilder1626
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

by:
barry houdini earned 500 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Expert Comment

by:Martin Liss
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

by:barry houdini
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 46

Expert Comment

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

Author Closing Comment

by:cansevin
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 46

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now