Home

Excel End of Month Question

%3CLINGO-SUB%20id%3D%22lingo-sub-737054%22%20slang%3D%22en-US%22%3EExcel%20End%20of%20Month%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737054%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20be%20able%20to%20have%20Excel%20automatically%20add%20a%20date%20based%20on%20another%20set%20of%20data.%26nbsp%3B%20For%20example%2C%20we%20have%20a%20production%20date%20of%207%2F3%2F2019.%26nbsp%3B%20The%20shelf%20life%20in%20most%20cases%20is%204%20years%2C%20but%20there%20are%20some%20that%20expire%20in%206%20months%2C%201%20year%20or%202%20years%2C%20etc..%26nbsp%3B%20Our%20expiration%20date%20is%20always%20the%2030th%20of%20the%20month%2C%20except%20for%20February%20which%20is%20the%2028th.%26nbsp%3B%20Is%20there%20a%20formula%20that%20will%20allow%20me%20to%20tell%20Excel%20to%20automatically%20add%20the%2028th%20or%2030th%20of%20the%20month%2C%20and%20the%20appropriate%20amount%20of%20years%20based%20on%20the%20production%20date%20I%20type%20in%3F%26nbsp%3B%20So%20for%20this%20example%2C%20the%20production%20date%20is%207%2F3%2F2019.%26nbsp%3B%20My%20expiration%20date%20is%207%2F30%2F2021.%26nbsp%3B%20I%20don't%20think%20EOMONTH%20will%20work%20as%20it%20will%20give%20me%20the%20absolute%20end%20of%20month.%26nbsp%3B%20Any%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-737054%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-737074%22%20slang%3D%22de-DE%22%3ERe%3A%20Excel%20End%20of%20Month%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737074%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354533%22%20target%3D%22_blank%22%3E%40Sue_G%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DEOMONTH(A1%2C12)%2BIF(DAY(EOMONTH(A1%2C12))%26gt%3B29%2C30%2C28)-DAY(EOMONTH(A1%2C12))%3C%2FPRE%3E%0A%3CP%3Eif%20we%20take%2012th%20months%20period%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738705%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20End%20of%20Month%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738705%22%20slang%3D%22en-US%22%3EIf%20production%20date%20is%20in%20A2%2C%20and%20expiry%20months%20is%20in%20B2%2C%20try%20this%20formula%20in%20C2%20to%20return%20the%20expiry%20date%3A%3CBR%20%2F%3E%3DEOMONTH(A2%2CB2)-%3CBR%20%2F%3EOR(MONTH(A2)%3D%7B1%2C3%2C5%2C7%2C8%2C10%2C12%7D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738761%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20End%20of%20Month%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738761%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20if%20only%20in%20leap%20years%20it's%20not%20Feb%2028%20required%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738833%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20End%20of%20Month%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738833%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20astute%20remark%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E.%20I%20wilfully%20ascribed%20the%20expiry%20date%20a%20liberal%20construction%20as%20to%20mean%20a%20day%20not%20later%20than%20the%2030th.%20Thus%2C%20such%20date%20may%20be%20the%2029th%20of%20February%20during%20leap%20years.%3CBR%20%2F%3EIf%20a%20strict%20construction%20is%20to%20pursued%2C%20such%20that%20expiry%20dates%20in%20February%20always%20occur%20on%20the%2028th%2C%20my%20formula%20shall%20be%20slightly%20longer%20with%20an%20addition%20of%20another%20logical%20test%2C%20like%20this%3A%3CBR%20%2F%3E%3DEOMONTH(A2%2CB2)-%3CBR%20%2F%3EOR(MONTH(A2)%3D%3CBR%20%2F%3E%7B1%2C3%2C5%2C7%2C8%2C10%2C12%7D)-%3CBR%20%2F%3E(DAY(EOMONTH(A2%2CB2))%3D29)%3C%2FLINGO-BODY%3E
Sue_G
Occasional Contributor

I want to be able to have Excel automatically add a date based on another set of data.  For example, we have a production date of 7/3/2019.  The shelf life in most cases is 4 years, but there are some that expire in 6 months, 1 year or 2 years, etc..  Our expiration date is always the 30th of the month, except for February which is the 28th.  Is there a formula that will allow me to tell Excel to automatically add the 28th or 30th of the month, and the appropriate amount of years based on the production date I type in?  So for this example, the production date is 7/3/2019.  My expiration date is 7/30/2021.  I don't think EOMONTH will work as it will give me the absolute end of month.  Any suggestions?

4 Replies

@Sue_G 

That could be like

=EOMONTH(A1,12)+IF(DAY(EOMONTH(A1,12))>29,30,28)-DAY(EOMONTH(A1,12))

if we take 12th months period

 

If production date is in A2, and expiry months is in B2, try this formula in C2 to return the expiry date:
=EOMONTH(A2,B2)-
OR(MONTH(A2)={1,3,5,7,8,10,12})
Highlighted

@Twifoo , if only in leap years it's not Feb 28 required

Thanks for your astute remark, @Sergei Baklan. I wilfully ascribed the expiry date a liberal construction as to mean a day not later than the 30th. Thus, such date may be the 29th of February during leap years.
If a strict construction is to pursued, such that expiry dates in February always occur on the 28th, my formula shall be slightly longer with an addition of another logical test, like this:
=EOMONTH(A2,B2)-
OR(MONTH(A2)=
{1,3,5,7,8,10,12})-
(DAY(EOMONTH(A2,B2))=29)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 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
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies