• 411K Members
• 5,069 Online
• 466K Conversations

## Complex IF Statement - Excel

Occasional Visitor

# Complex IF Statement - Excel

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.

2 Replies

# RE: Complex IF Statement - Excel

=IF(E9=EOMONTH(E9,-1)+1,TEXT(E9,"dd/mm/yyyy"),EOMONTH(E9,0)+1)

# Re: Complex IF Statement - Excel

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