6 Replies Latest reply on Mar 28, 2016 1:53 PM by george dudley

# Emulate Excel Formula

Hello,

I am trying to emulate the phrase EOMONTH(TODAY(),0)+1 from Excel in Tableau and I can't seem to establish the End Of Month.

Actually I am trying to emulate =IF(ISBLANK(L11),"",IF(EOMONTH(TODAY(),0)+1>EDATE(K11,0),IF(ISBLANK(K11),"No expiration","Verify"),"Correct")), but I think I can do the rest of it, I just can't get the EOM.

Any help would be appreciated.

Thanks, George

• ###### 1. Re: Emulate Excel Formula

Hey George,

I'm a bit rusty on my excel, but it looks like that is getting the start of next month, right? (EOM + 1 day?)

If so, you could do a combo of DATETRUNC and DATEADD. If not, just add another DATEADD to subtract 1 day.

Something like this for the start of next month:

DATEADD('month', 1, DATETRUNC('month', TODAY()))

Or EOM:

Hope this helps!

- Derrick

1 of 1 people found this helpful
• ###### 2. Re: Emulate Excel Formula

Wow!  I wasn’t expecting such a quick
response.  Okay, I’m not rusty on my
Excel, I just don’t know it…and I don’t know Tableau, either, but I’m the guy
they chose for this project.  Anyway yes,
we are basically getting the first of next month so we can check a bunch of
lease expirations and see if they are imminent.

So looking at your solution

DATEADD('month', 1, DATETRUNC('month', TODAY())) , what you are doing
here is saying we will take today’s date, truncate it to the current month
(which is returned as the first of the month), and then adding a month to
it.  Right?  That’s just devious.

• ###### 3. Re: Emulate Excel Formula

Yep - you are correct. That is exactly what it is doing.

• ###### 4. Re: Emulate Excel Formula

You are awesome.  Thanks.

Do you get points or karma or something for this, and if so how do I make sure it's awarded?

• ###### 5. Re: Emulate Excel Formula

Haha, no problem!

The forum does indeed award "points".

The key is to mark the correct reply "correct" and any helpful responses "helpful".

• ###### 6. Re: Emulate Excel Formula

I haven't done a thorough check, but at first glance it actually works.  You don't know what a shock this is to me.

Just in case you're interested in my simple-assed calculation, here it is.

Thank you!

If ISNULL([Expense Type])THEN "-BLANK-"

ELSEIF DATEADD('month', 1, DATETRUNC('month', TODAY()))THEN "Verify"

ELSEIF ISNULL([Expiration]) THEN "No Expiration"

ELSE "Correct"

END