 • 669K Members
• 9,427 Online
• 825K Conversations

Highlighted

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

# RE: Complex IF Statement - Excel

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

# Re: Complex IF Statement - Excel

Use this formula:
=IF(DAY(E9)=1,
E9,
EOMONTH(E9,0)+1)