Hello,

I have sheet where date is expressed as day of a year.
I know that converting from day-month-year into day of a year is quite
easy, but I don't know how to do this in other direction.

I have tried to write multiple if confitions, but it's generating many
errors.
Is there any convenient way of doing it (maybe macro ?)

> I have sheet where date is expressed as day of a year.
> I know that converting from day-month-year into day of
> a year is quite easy, but I don't know how to do this in
> other direction.

I would think you are looking for this...

=DATE(YEAR(NOW()),1,<<DayOfYear>>)

where you would put the number of the "day of year" or a cell reference
containing it.

Rick Rothstein (MVP - Excel)

On Tue, 08 Mar 2011 08:20:13 +0100, Nateysz wrote:
> I have sheet where date is expressed as day of a year.
> I know that converting from day-month-year into day of a year is quite
> easy, but I don't know how to do this in other direction.
>
> I have tried to write multiple if confitions, but it's generating many
> errors.
> Is there any convenient way of doing it (maybe macro ?)

If the day-of-year is in A1 and the year is in A2, then use this
formula:

=date(A2, 1, A1)

Excel will take e.g. the 364th day of January and convert it to
December 30 (or 29, in leap years).

I've tested this in Excel 2010, but AFAIK it works in earlier Excels
too.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

> If the day-of-year is in A1 and the year is in A2, then use this
> formula:
>
> =date(A2, 1, A1)
>
> Excel will take e.g. the 364th day of January and convert it to
> December 30 (or 29, in leap years).
>
> I've tested this in Excel 2010, but AFAIK it works in earlier Excels
> too.

It works even in Open Office Calc.