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
Highlighted
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

 

Highlighted
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

Highlighted
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
Help with simple formulas please
nursekimberley in Excel on
2 Replies
Excel or Access? basic advice from which to start from.
grifton in Excel on
2 Replies
Goal seek
Abui_2195 in Excel on
3 Replies
Preparing a stacked chart
EuroSree in Excel on
2 Replies
Please Help debug my matrix formula
Katharina_Stemmer1989 in Excel on
1 Replies
Linking external source (eg Share Price) to a cell
Aitch1964 in Excel on
1 Replies