Nested IF with dates and text return string

**Mikeyabosbht** · 08-07-2017, 01:16 PM

Hi Guys,

This is quite complicated.

I need to compare dates between two columns and return various types of text string.

We have an assessment date which can vary and is always based on the first day of the month in a year. This needs to be compared to an earnings period date and the two dates will never coincide:

Like so:

Nested IF help.png

It might not actually have an assessment date and I would like it to return " " or Blank space.

If it falls on the 18th of the same month or subsequent months, I need it to return "Subsequent"

If it falls on the 18th of the prior month to the assessment date, I need it to return "At Staging"

If it falls on the 18th MORE THAN one month prior to the assessment date, I need it to return "Error"

What might complicate things is there an alternate number of days based on the actual month we are in?

Thanks so much for your help!

**FDibbins** · 08-07-2017, 01:28 PM

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

**Mikeyabosbht** · 08-07-2017, 01:35 PM

It is not allowing me to upload the attachment when I press the paperclip?

Is there an alternate way?

thanks

**FDibbins** · 08-07-2017, 01:43 PM

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.

**Mikeyabosbht** · 08-07-2017, 01:57 PM

Hopefully this is okay.

Thanks for your help.

**kev_** · 08-07-2017, 01:59 PM

=IF(ISBLANK(F3),"",IF(F3<E2,"Error",IF(F3=E2,"At Staging",IF(F3>E2,"Subsequent"," "))))
(could also use a lookup table to avoid nested ifs)

see attached for contents of column D to give days in month

nestedifs.jpg

EDIT
Just noticed you have posted a file - I posted this before I spotted that - so ignore your file and use mine

**Mikeyabosbht** · 08-07-2017, 02:12 PM

Thanks very much, Kev.

The items highlighted in red need to be the 1st of every month.

Row 2-4 looks great. Row 5 would need to say subsequent, Row 6 should say error and Row 8 should say subsequent

I've added in green an example of what it should be like staging as well.

It complicated, i'm sorry.

**kev_** · 08-07-2017, 02:35 PM

I think I may have done quite the opposite to what you want - are all the formulas the wrong way round?
Will look later!!

**Mikeyabosbht** · 08-07-2017, 02:37 PM

Sorry for the double-post.

Okay the additional attachment may have made it a little clearer.

Any help greatly appreciated

**JeteMc** · 08-07-2017, 05:25 PM

Here is a modification of Kev's formula that yields the values that were manually placed in column C of the file attached to post #9:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**kev_** · 08-08-2017, 12:38 AM

Virtually identical to @JeteMc solution.
Only real difference is that "staging" condition is not defined - it's the result of the value failing all the other conditions

Some date formulas become unfathomable

- it can lead to a lot of subsequent time wasting (and frustration

) if user wants to amend something
- so I generally make use of helper columns (here to calculate the days in the month of Earnings period)

with Column A = Earnings period, Column B = Assessment data, Column E helper

with helper column E:
=IF(ISBLANK(B2),"",IF(B2<A2,"subsequent",IF(B2>=(A2+E2),"Error","staging")))

without helper column:
=IF(ISBLANK(B2),"",IF(B2<A2,"subsequent",IF(B2>=(A2+DAY(DATE(YEAR(A2),MONTH(A2)+1,))),"Error","staging")))

NOTE:
Before trusting these formula I suggest you test the 17th,18th,19th to make sure cut-off is correct for each category

**Mikeyabosbht** · 08-08-2017, 02:41 PM

Thanks so much, Guys. Really grateful!

If there was a further scenario, like I have attached above, where the staging was equal to the assessment date. how would the formula alter?

It is just like before but where staging is exactly equal to assessmment date it needs to return staging.

Thanks so much!

**JeteMc** · 08-08-2017, 04:48 PM

I believe that the formula in post #10 already does what you want. In the five instances of the file attached to post #12 where the assessment date and staging date are the same the formula returns 'At Staging'.
Let us know if you have any questions.

**Mikeyabosbht** · 08-09-2017, 07:46 AM

Hi Guys,

Many thanks for your help. Yeah, I saw that after. My apologies.

If we have a future staging date, say 01/01/2018 and NO assessment date. The assessment date will be blank and the staging date will say, 01/01/2018

The return string is still "error" even though it is in the future and the assessment action has not transpired yet. How can I alter the above formula so it also uses a blank return or " " when there is a future staging, but no assessment date?

The current procedure is to trap historical assessment that is more than one month prior to the staging with the error. How can I trap future staging with no current assessment date with a " " rather than an error?

Thanks so much!

**kev_** · 08-09-2017, 07:57 AM

Try nesting whichever formula you used inside another IF
=IF(ISBLANK(A2) , "" , current formula)

Using formula post 11 as an example,
this:
=IF(ISBLANK(B2),"",IF(B2<A2,"subsequent",IF(B2>=(A2+E2),"Error","staging")))

becomes this:
=IF(ISBLANK(A2),"",IF(ISBLANK(B2),"",IF(B2<A2,"subsequent",IF(B2>=(A2+E2),"Error","staging"))))

**Mikeyabosbht** · 08-09-2017, 02:18 PM

That's a great help. Thanks so much.

**Mikeyabosbht** · 08-16-2017, 02:36 PM

Hi Guys,

Got a small follow up and would appreciate your help, please

I have attached the spreadsheet along with the original formula. Adjacent to that is the string that I need to return.

If assess is one month prior to SD I need it to return "staging"

If assess is more than one month prior to SD I need it to return "Error"

If assess is equal to or in excess of SD, I need it to return "subsequent".

Would really appreciate any help. I have tinkered, but I am not sure what I am doing with nested formulas.

Thanks so much

**JeteMc** · 08-16-2017, 05:23 PM

The following formula, pasted into cell D3 and copied down, yields the same results as those marked 'Needs to say' in the file attached to post #17:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**Mikeyabosbht** · 08-18-2017, 03:42 PM

jet, you have been great. Thanks so much.

**JeteMc** · 08-21-2017, 09:40 AM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

Nested IF with dates and text return string

LinkBack

Thread Tools

Rate This Thread

Display

Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Re: Nested IF with dates and text return string

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find partial text string within another text string return the full value

Search text string and return multiple results depending on content of text string

[SOLVED] Search for a text string and return given text string to adjacent cell

[SOLVED] IF range of cells contains string of text, return contents of cell where string is found

[SOLVED] Find partial text string within another text string return original text into cell.

[SOLVED] Nested IF statements to FIND text string and return set values.

I need a function that will return a value nested in a text string

Bookmarks

Bookmarks

Posting Permissions