Home

Complex IF Statement - Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-379712%22%20slang%3D%22en-US%22%3EComplex%20IF%20Statement%20-%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-379712%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20build%20a%20calculator%20for%20my%20team%20to%26nbsp%3B%20calculate%20benefit%20insurance%20effective%20dates%20based%20on%20an%20employees%20hire%20date.%20The%202%20options%20are%20%22First%20of%20month%20after%20waiting%20pd.%22%20or%20%22First%20of%20month%20%3CEM%3Ecoinciding%20with%3C%2FEM%3E%20or%20after%20waiting%20pd.%22%20I%20have%20the%20formula%20to%20calculate%20first%20of%20month%20after%20but%20I%20can't%20figure%20out%20how%20to%20do%20%22coinciding%20with%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20IF%20statement%20I%20wrote%20is%26nbsp%3B%3DIF(E9%3DDATE(YEAR(E9)%2CMONTH(E9)%2C1)%2C%3DE9%2C%3DDate(year(E9)%2CMonth(E9)%2B1%2C1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20keep%20the%20quotation%20marks%20in%3A%20%3DIF(E9%3DDATE(YEAR(E9)%2CMONTH(E9)%2C1)%2C%22%3DE9%22%2C%22%3DDate(year(E9)%2CMonth(E9)%2B1%2C1)%22)%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20returns%20the%20text%20string%20without%20a%20problem%20but%20I%20need%20it%20to%20return%20the%20actual%20result%20of%20the%20formula%20instead%20of%20just%20the%20text.%3C%2FP%3E%3CP%3EIf%20true%20(if%20the%20date%20in%20E9%20is%20the%20first%20of%20the%20month)%2C%20I%20need%20it%20to%20return%20the%20%3CEM%3Evalue%3C%2FEM%3E%20of%20cell%20E9.%20If%20false(if%20it's%20not%20the%201st%20of%20the%20month%2C%20I%20need%20it%20to%20return%20the%20date%20as%20first%20of%20the%20following%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-379712%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EIF%20formula%20combo%20error%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-380631%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20IF%20Statement%20-%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-380631%22%20slang%3D%22en-US%22%3EUse%20this%20formula%3A%3CBR%20%2F%3E%3DIF(DAY(E9)%3D1%2C%3CBR%20%2F%3EE9%2C%3CBR%20%2F%3EEOMONTH(E9%2C0)%2B1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-379758%22%20slang%3D%22en-US%22%3ERE%3A%20Complex%20IF%20Statement%20-%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-379758%22%20slang%3D%22en-US%22%3E%3DIF(E9%3DEOMONTH(E9%2C-1)%2B1%2CTEXT(E9%2C%22dd%2Fmm%2Fyyyy%22)%2CEOMONTH(E9%2C0)%2B1)%3C%2FLINGO-BODY%3E
Meridith
Occasional Visitor

I'm trying to build a calculator for my team to  calculate benefit insurance effective dates based on an employees hire date. The 2 options are "First of month after waiting pd." or "First of month coinciding with or after waiting pd." I have the formula to calculate first of month after but I can't figure out how to do "coinciding with". 

 

The IF statement I wrote is =IF(E9=DATE(YEAR(E9),MONTH(E9),1),=E9,=Date(year(E9),Month(E9)+1,1))

 

If I keep the quotation marks in: =IF(E9=DATE(YEAR(E9),MONTH(E9),1),"=E9","=Date(year(E9),Month(E9)+1,1)") 

it returns the text string without a problem but I need it to return the actual result of the formula instead of just the text.

If true (if the date in E9 is the first of the month), I need it to return the value of cell E9. If false(if it's not the 1st of the month, I need it to return the date as first of the following month.

 

Any advice? 

 

2 Replies
=IF(E9=EOMONTH(E9,-1)+1,TEXT(E9,"dd/mm/yyyy"),EOMONTH(E9,0)+1)
Use this formula:
=IF(DAY(E9)=1,
E9,
EOMONTH(E9,0)+1)
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
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies