How to convert day of a year

[Nateysz]

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 ?)

 

[Rick Rothstein]

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)

 

[Stan Brown]

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.

 

[Nateysz]

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.