How to convert day of a year

  • Thread starter Thread starter Nateysz
  • Start date Start date
N

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 ?)
 
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.
Click to expand...

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)
 
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 ?)
Click to expand...

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.
 
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.
Click to expand...

It works even in Open Office Calc.
 
Back
Top