Convert years Nth date back to calendar date.

I am a Excel user on Apple. I am trying to generate an average date during a year. My base data is many dates over several years time. I can isolate the Nth day of the year for each event. I ca...

excel

Kodipady
Jun 30, 2019
Peter_Kelley,
=TEXT(DATE(YEAR(TODAY()),1,1)+NthDay, "MMM D") should be able to address this. This adds the NthDay to the beginning of current year, converts it back to the format you need (Example "May 2"). Please substitute NthDay with the average day of occurrence.

Please note that the formula will give you different result on a leap year, just that this formula is year specific. if you want to avoid that situation, simply replace the formula with following : =TEXT(DATE(2019,1,1)+121, "MMM D").

Kodipady

Iron Contributor

Jun 30, 2019

Peter_Kelley,

=TEXT(DATE(YEAR(TODAY()),1,1)+NthDay, "MMM D") should be able to address this. This adds the NthDay to the beginning of current year, converts it back to the format you need (Example "May 2"). Please substitute NthDay with the average day of occurrence.

Please note that the formula will give you different result on a leap year, just that this formula is year specific. if you want to avoid that situation, simply replace the formula with following : =TEXT(DATE(2019,1,1)+121, "MMM D").

Peter_Kelley
Copper Contributor
Jun 30, 2019
KodipadyThank you for the simple reply. You've made a rough day a little better.

Forum Discussion

Convert years Nth date back to calendar date.

Resources