Return EDATE with an IF statement...? [SOLVED]

**mirage1** · 01-21-2013, 01:25 PM

I searched the forums for EDATE but I didn't see anything that helped me, quite.

This is the current formula:

=IF(R2<20,"",IF(M2="Pre-QA",N2,IF(M2="Pending",N2,IF(U2=0,N2,O2))))

Where N2 and O2 are both dates.

I want the formula to change those to the end of the month for this cell but I can't figure out where to/how to put the EDATE bit in.

Help? Thanks!

**FDibbins** · 01-21-2013, 01:37 PM

Hi mirage1 and welcome to the forum

Im not exactly sure what you are trying to achieve, but edate() calcs a date x mths before or after a given date?

This is not what you want, but your formula could be simplified into...
=IF(R2<20,"",IF(and(M2="Pre-QA",M2="Pending",U2=0),N2,O2))

Perhaps if you uploaded a workbook, with a few samples of your expected outcome, we would be able to see what you wanted, and offer some suggestions?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**bebo021999** · 01-21-2013, 01:57 PM

Just simplify your formula as per #2 and replace N2 with EDATE(N2,n) with n is number of months you wish to add(or substract). Same for O2.

**mirage1** · 01-21-2013, 02:01 PM

I have never used an "and" statement, I need to learn! Also, maybe the thing I really wanted was EOMONTH?

The formula is in column Q.

The ultimate goal is to get the pivot chart to act properly. I want the data grouped by the month and year that is in column Q, but I can't group by month in the pivot table (I think because there are some fields that are blank?). So what I end up doing every week is using this formula to get the date, then going through column Q and manually changing them all so they actually show the first of the month (which day of the month doesn't matter, I just want them to group right in the pivot table). I realize that is completely silly!

**mirage1** · 01-21-2013, 02:15 PM

The simplified formula changes the results, actually. When it's POST QA I need it to return either N, or O, depending on whether U=0 or not.

And it seems EDATE isn't really what I needed! But, I tried replacing the N2 and O2 with EOMONTH(N2,0) and EOMONTH(O2,0) and that worked like a charm. Thank you!

(Edited to add - I just realized that you couldn't tell from the original formula that there was a third possibility in column M. The simplified formula probably would have worked fine, without that.)