SOLVED
Home

Convert years Nth date back to calendar date.

%3CLINGO-SUB%20id%3D%22lingo-sub-730024%22%20slang%3D%22en-US%22%3EConvert%20years%20Nth%20date%20back%20to%20calendar%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730024%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20a%20Excel%20user%20on%20Apple.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20generate%20an%20average%20date%20during%20a%20year.%26nbsp%3B%20My%20base%20data%20is%20many%20dates%20over%20several%20years%20time.%20I%20can%20isolate%20the%20%3CSTRONG%3ENth%20day%3C%2FSTRONG%3E%20of%20the%20year%20for%20each%20event.%26nbsp%3B%20I%20can%20generate%20an%20average%20day%20of%20the%20occurrence%20from%20those%20Nth%20days.%26nbsp%3B%20But%20I%20find%20myself%20stumped%20as%20to%20how%20to%20take%20that%20averaged%20Nth%20date%20to%20convert%20back%20to%20a%20calendar%20date%20(example%3A%203%2F14%20or%20March%2014th).%26nbsp%3B%20if%20you%20really%20need%20to%20know%20what%20I%20am%20doing%20...%20I%20am%20following%20garden%20dates%20of%20my%20plants.%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20someone%20tell%20me%20how%20to%20do%20achieve%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-730024%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730123%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20years%20Nth%20date%20back%20to%20calendar%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368833%22%20target%3D%22_blank%22%3E%40Peter_Kelley%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%3DTEXT(DATE(YEAR(TODAY())%2C1%2C1)%2B%3CEM%3ENthDay%3C%2FEM%3E%2C%20%22MMM%20D%22)%26nbsp%3B%20should%20be%20able%20to%20address%20this.%26nbsp%3B%20This%20adds%20the%20NthDay%20to%20the%20beginning%20of%20current%20year%2C%20converts%20it%20back%20to%20the%20format%20you%20need%20(Example%20%22May%202%22).%20Please%20substitute%20%3CEM%3ENthDay%3C%2FEM%3E%20with%20the%20average%20day%20of%20occurrence.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20the%20formula%20will%20give%20you%20different%20result%20on%20a%20leap%20year%2C%20just%20that%20this%20formula%20is%20year%20specific.%20if%20you%20want%20to%20avoid%20that%20situation%2C%26nbsp%3B%20simply%20replace%26nbsp%3Bthe%20formula%20with%20following%20%3A%26nbsp%3B%20%3DTEXT(DATE(2019%2C1%2C1)%2B121%2C%20%22MMM%20D%22).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730308%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20years%20Nth%20date%20back%20to%20calendar%20date.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3EThank%20you%20for%20the%20simple%20reply.%26nbsp%3B%20You've%20made%20a%20rough%20day%20a%20little%20better.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Peter_Kelley
New Contributor

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 can generate an average day of the occurrence from those Nth days.  But I find myself stumped as to how to take that averaged Nth date to convert back to a calendar date (example: 3/14 or March 14th).  if you really need to know what I am doing ... I am following garden dates of my plants.

Can someone tell me how to do achieve this?

2 Replies
Highlighted
Solution

@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").  

@KodipadyThank you for the simple reply.  You've made a rough day a little better.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies