IF text string is equal left characters and DDMM of DOB is outside a range.

**DaveBre** · 05-07-2014, 12:31 AM

Hi,

I have a multiple vlookup I'm trying to accomplish and I'd appreciate some help.

Basically I have I need the formula to check the following.

Firstly using IF/Left does the start of a text string have the characters ABC for the first 3, if so then VLOOKUP cell B1 to determine if the persons DDMM of their birth is in the 3 days after the last day of the end of the period in cell B1 (held in a named range called paycals)

I've started the formula but I don't know how to tackle the 3 days part.

Please Login or Register  to view this content.

Would anybody have anything similiar? I've attached an example file.

Thanks

**FDibbins** · 05-07-2014, 12:44 AM

See if this will get you heading in the right direction. The formula below will find the date and to the test for you, you just need to finish it off with the ABC part....

=VLOOKUP($B$1,'Pay Calendars'!$A$2:$C$27,3,0)<=DATE(2014,MONTH($B$3),DAY($B$3))+3

**Saarang84** · 05-07-2014, 01:31 AM

Hi Dave,

You could place a date in the Pay Period column in the Pay Calendars sheet and set its format to dd/yyyy and try using it in the vlookup.
Do you want to set the Pay Period format as "dd/yyyy"?

It would be better, if you can give us an example of what the input and the corresponding output would be.

**DaveBre** · 05-07-2014, 01:54 AM

Hi Saarang84

Unfortunately the pay period (23/2013) will change over time, and it can't be moved from (cell B1).

It can be formatted as dd/yyyy, as can all of the pay period in column A of the pay calanders sheet. Is that what you mean?

I'm not sure what you mean by input and output, the only two inputs are does the first 3 characters of the text string in C3 = "ABC", and if so is the end date of the pay period (23/2013)+3 days <= the DDMM of the DOB. The outpit is simply a Yes or blank, or any other text string.

Does that help?

Thanks

**Saarang84** · 05-07-2014, 02:07 AM

I think you've not understood my question. Let me be more clear.

The Pay Period looks like its in the format dd/yyyy. How are you generating it? It corresponds to the Pay Period column in Pay Calendars sheet, but what does it signify ? Can't it be generated from a date ? (Say, for e.g., 5/7/2014 can be formatted as 07/2014 and can be used for vlookup)

I asked for an example, bcoz, you have mentioned a DOB date in the Calculations sheet. What is the corresponding value you want from Pay Calendars sheet for this date?

**DaveBre** · 05-07-2014, 02:14 AM

Ok, I understand now.

It's a fortnightly pay period (1-26) followed by the financial year (1/7/13 - 30/6/14). So this instance is is pay 23 of the 13/14 finacial year and the dates in columns B & C are the first andlast dates of the range.

Generally it would just be text, but it has no relation to dd/yyyy really.

**FDibbins** · 05-07-2014, 02:16 AM

Did you see post # 2

**kvsrinivasamurthy** · 05-07-2014, 02:32 AM

Pl see attached file.

**DaveBre** · 05-07-2014, 02:36 AM

Hi FDibbins,

Yes I did see post two but I'm still trying to get it to work as it's not doing what I expected in terms of terms of returning a Yes/No.

The amended formula is

Please Login or Register  to view this content.

However this is returning the wrong answer, I think it needs to be if the DDMM part of the birthday is >= is the Vlookup date +1 (5/5) but =< Vlookup date +3 (7/5)

**DaveBre** · 05-07-2014, 02:42 AM

Hi All

kvsrinivasamurthy seem to have solved this, so thanks kvsrinivasamurthy.

Also thanks FDibbins and Sarrang84. Much appreciated all of you.

**FDibbins** · 05-07-2014, 11:39 AM

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**DaveBre** · 05-07-2014, 06:53 PM

Hi FDibbins,

Thanks for the reminder I did intend to close it yesterday but it slipped my mind.

Thanks again to everybody who contributed to a solution.

IF text string is equal left characters and DDMM of DOB is outside a range.

LinkBack

Thread Tools

Rate This Thread

Display

IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Re: IF text string is equal left characters and DDMM of DOB is outside a range.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Insert multiple characters into string w LEFT RIGHT functions

[SOLVED] Extract left characters from string with exception of 2 right characters

removing 4 characters from the left of a string

want to remove all text characters equal to one character in length from text string

want to remove all text characters equal to one character in length from text string

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions