Vlookup Dates
Hi Everybody,
I have actually two file but for the sake of example I just put them in two sheets. I receive the information which is the source and I need to have the Mob/demob date automatically depending on the information from the source. There are hundreds of rows in the source and I'm looking for a faster solution that will automatically calculate the mob/demob date anytime there are changes in the source.
I've attached a sample file and explained also the problem. Pls. let me know if there's clarification needed.
Appreciate any help.
Last edited by rickyilas; 03-06-2010 at 03:37 AM.
Hi,
Combining the index() and match() functions ...
see attached starting point ...
HTH
Thanks Jean... I now have the mobilization date (I just had to change the header date from the source so it will show the 1st day of the month. What I need now is the demob date which should be the end of the month. Pls. watch out for the .5 which signifies, it started in beginning half of the month or in case of demob, ends half of the month.
Hi,
See attached for second demob formula ...
I have not included the test for the value to determine the "half month" ...
HTH
Hi Jean,
Thanks for your answers, by the way is it possible to be getting the last day of the month on the Demob date? Because after getting this Mob/Demob date, I need to get the total days so it's important that I get the actual end date.
G'day,
I fear the suggestion I have attached to this post is a bit to much.... but it works.
But in saying that I don't speak VBA lanuage.
Basically with a few helper columns and a database sheet the required answers return in the correct cell.
The formulas is indexing the unique badge id and then finding the first cell and the last cell only with information in that row.
Please do not be shy in asking any questions
Cheers
RC
Have I made you happy ??? If yes, please make me happy by pressing the Reputation icon in my post.
Please don't forget to do the same to other contributors of this forum.
Thanks
I don't void confusion, I create it
Great Job!!! Thanks Ratcat! I'm getting the mob (beginning or from half of the month) correct. However in the demob date I'm getting always the end of the month even if it is .5. It's probably because of what I explained below and I quote:
QUOTE Thanks Jean... I now have the mobilization date (I just had to change the header date from the source so it will show the 1st day of the month. What I need now is the demob date which should be the end of the month. Pls. watch out for the .5 which signifies, it started in beginning half of the month or in case of demob, ends half of the month. End Quote
What I will need in the demob is if it is .5 means from 1st to 15 of the month so end date should be 15th of the month. It will only be 30th or 31st if the value is 1 which signifies 1 whole month.
I've attached an updated file of what you did without the blanks that's giving errors. I'm just wondering if there's anyway where we could just multiply the 1 or .5 on the number of months. (ex. in Project Manager in Source). If not your formula works just fine, just a modification in the demob date pls.
G'day Ricky,
I had a look and wondering why you where asking for the modification on the demob date. I had I look and silly old me when I was referencing the cell I referenced the wrong one.
The formula on 'Sheet1' at F4 cell should be this
Code:=IF(ISNA(N4),"",N4)
copy drag down and if you wish to change the half months from the 15th or 16th...go to the database sheet and change the Result column (Column D) and change the dates there for your requirements.
HTH
Cheers
RC
Have I made you happy ??? If yes, please make me happy by pressing the Reputation icon in my post.
Please don't forget to do the same to other contributors of this forum.
Thanks
I don't void confusion, I create it
RC, Thank a lot!!!!!! This will really speedy up my work!
As per private inquiry, here is an idea for the sheet with no addon columns or sheets.
1) Make sure the TOOLS > ADDINS > Analysis ToolPack is installed and enabled
2) Formulas:
In E4 and copied down:
=EOMONTH(INDEX(SOURCE!$D$3:$AA$3, MATCH(TRUE, INDEX(ISNUMBER(SOURCE!$D4:$AA4), 0), 0)), -1) + LOOKUP(INDEX(SOURCE!$D4:$AA4, MATCH(TRUE,INDEX(ISNUMBER(SOURCE!$D4:$AA4), 0), 0)), {0.5,1}, {16,1})
In F4 and copied down:
=IF(INDEX(SOURCE!$D4:$AA4, MATCH(2, SOURCE!$D4:$AA4,1))=1, INDEX(SOURCE!$D$3:$AA$3, MATCH(2, SOURCE!$D4:$AA4, 1)), EOMONTH(INDEX(SOURCE!$D$3:$AA$3, MATCH(1, SOURCE!$D4:$AA4, 1)), 0) +15)
Last edited by JBeaucaire; 03-08-2010 at 07:52 AM. Reason: 2nd formula updated
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks Jerry, it's almost working perfectly. Pls. notice that anybody that is ending 0.5 is returning the previous month value. Ex. Project Mgr. supposed to end Jul 15 but in Sheet1 is returning value is Jun15.
Correction noted above in post #9. Sample sheet amended as well.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Now its perfect! Thanks Jerry!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks