Day in year

[md2011]

Is there a function in excel where i could get the day in year( out of 365) from the month in the year and day in month?

so if today was month=9 and day =30 then output day in year =273

or is there a way I can have a cell which automatically updates the day in year value.

The month and day in month values are calculated separately using the =NOW()


Many Thanks for any help

Matt

[daddylonglegs]

It's easier to do that directly using TODAY() function

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

format result cell as general

[alansidman]

My suggestion is to use the DateDif function.

http://www.techonthenet.com/excel/formulas/datedif.php

Compare the date in question to the first day of the year.

Alan

[md2011]

I added :

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

into my sheet and it works the result is 273 days.

Will this update everyday by itself? We are running a programme with output and the day in month and month generate when the sheet is opened , will this day in year value do the same?

I don't know how it works but it works and you have been a great help!

[md2011]

so the calculation looks at the difference from the 31st Dec 2010 to Today which is obviously the year day!

[daddylonglegs]

That's right. This formula gives you 1st January in the current year

=DATE(YEAR(TODAY()),1,1)

but notice that I used zero in place of the last 1.....which gives you the day before, i.e. last day of last year, possibly more transparent would be

=DATE(YEAR(TODAY())-1,12,31)

and, of course it will update every day as long as you re-open or re-calculate the sheet.

Alan's suggestion is similar but you don't really need DATEDIF to get a difference in days - you can just subtract as with my suggestion