Excel 2007 - Formula Give number for date

[geospatial]

I have an worksheet that does a few things. First in Cell "A1" I have the formula =if(Sheet1!$B$1=1,MIN(H6:H36),if(Sheet1!$B$1=2,MIN(H37:H64),"")

Column H is a list of dates beginning with "H6" at 1-Jan. So basically if in Cell "B1" I either have the number 1 or 2. If it is 1 it finds the first date of January, and if it is 2 it is the first date of February.

What I really need is a formula to go into Cell "A2" that would look at Cell "A1" find the date and then make that date a number depending on the day of year. So if "A1" is 1-Jan then in cell A2 it would show a 1, if it is 2-Jan it would show a 2 and so on.

 

[Joe4]

Take a look at Chip Pearson's write-up on Julian dates:
http://www.cpearson.com/excel/jdates.htm

 

[Joe4]

Chip's first post tacks on the two digit year to the front. If you want the numeric representation of the day of a particular year (but don't care about the year portion), just remove the first part and the TEXT function from his first formula to get this:

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

 

[geospatial]

Thanks,

I had used Chip Pearson site a lot in the past, just wasnt able to figure out how to make it go from year to day.

Thanks again for the help. Your solution worked great.

 

[Joe4]

Glad to help!