I am having issues formatting a date & time cell so that my VLOOKUP function works properly.

[BrigitteR]

Hi,

I am trying to run a cross-check in Excel 2010 between two different sets of data and I am having issues getting the (IFERROR(VLOOKUP)) function to work properly.

Because the data in both spreadsheets has different names for the same thing (i.g one spreadsheet uses the term "YSCINT" and the other uses the term "Crisis Episode") I am also using the CONCANTENATE formula to make the data being checked universal across both spreadsheets.

I have determined that the issue is in the start and end time column of my second spreadsheet. In the first spreadsheet, in the cell that has the CONCANTENATE formula, the start and end times appear as large numbers with decimal points. I realize that the large numbers with decimal points is the numeric value that Excel has assigned to that particular date and time. However, in the cell with the CONCANTENATE formula in the second spreadsheet, the dates and times do not show up as numeric values. They appear as text, which made me think there is something wrong with the formatting and Excel is not recognizing those cells as containing dates and times.

In the first spreadsheet the data in the start and end time cell appear as follows:

mm/dd/yy hh:mm:ss AM/PM

In the second spreadsheet the start and end times appear as follows:

mm/dd/yy hh:mm AM/PM

Notice how in the second spreadsheet it is missing the seconds. I have tried highlighting the cells that are formatted improperly using the "Format Cells" window. Under "Category" I choose "Date" and then I select the 6th one from the bottom: 3/14/01 1:30 PM

That option doesn't add the seconds to the time. Even though that is what I used to format the dates in my first worksheet, the one that is formatted properly.

I have found that if I select one of the improperly formatted date cells, and then click into the formula bar, and then click into another cell, when I go back to the cell I was in before, it suddenly becomes formatted properly.

I would like to know what is causing this, and how can I fix it without having to click into each cell and then click into the formula bar 700 times? Because that is very time consuming and I am sure there is a faster way to fix this.

Let me know if you need screenshots of my issue.

Thanks.

 

[Joe4]

Welcome to the Board!

It is difficult to see how everything is actually structured, by here are some tips on using VLOOKUP:

- The value you are looking and column you are matching it to MUST be the same data type. Specifically, you cannot have one be Number/Date/General (these three are really all the same data type) and the other Text. You cannot compare Text to Numbers in a VLOOKUP.

- If you do have a Number/Date/General value, apply different formatting (including custom formatting) does NOT change the value of the cells. It only changes how it is presented. Excel and VLOOKUP recognize the true value underneath and will use that. So this means if you have a value with seconds, and you choose a format that does not show seconds, VLOOKUP will still see and use the seconds in its comparison.

- If you have two Date values, and one has seconds in it and the other doesn't, they will not match unless you get rid of the seconds. I think the easiest way to do that would be to convert both columns to text, using the Text function like this:
=TEXT(A1,"mm/dd/yy hh:mm")
Then you will be comparing Text to Text, and the Text value will be exactly as they appear to you.

 

[jakeman]

To me it would seem that the values in the second spreadsheet were copied over as text values, instead of true date values. I would try something like this. Setup a temporary helper column called DateMod. If your existing date values are in column D (as an example), enter this formula in the helper column:

=DATEVALUE(TEXT(D2,"MM/DD/YYYY HH:MM:SS"))

That should convert it to the proper format and then you can copy/paste the helper column values back to the original column of date values...at least I think it would work. Can't duplicate your scenario perfectly

 

[Joe4]

Also forgot to mention.
Using functions like CONCATENATE will return a Text value, not a numeric/date one.

 

[BrigitteR]

To me it would seem that the values in the second spreadsheet were copied over as text values, instead of true date values. I would try something like this. Setup a temporary helper column called DateMod. If your existing date values are in column D (as an example), enter this formula in the helper column:

=DATEVALUE(TEXT(D2,"MM/DD/YYYY HH:MM:SS"))

That should convert it to the proper format and then you can copy/paste the helper column values back to the original column of date values...at least I think it would work. Can't duplicate your scenario perfectly

So with this we got close. When I use the formula above as written (swapping out the "D2" for the proper cell) I get a numeric value. However it is not a decimal. It is a whole number. So when I attempt to change it to a date, I get the right date, but the time is set to midnight. I have attempted to expand the number by multiple decimal places but no matter how far out I go, I just get .0000000, ect.

I don't know if this will help, but this is how my spreadsheets are set up. I had to hide certain columns and change names because the identifying information is protected by law. But hopefully you will get the gist of it.

So this is spreadsheet #1 (YSCM). This is the one that is working properly and has no issues.

A	B	C	E	F	H	J	K	N
Match?	YSCM Key	careNumber	lastName	firstName	Eventtype	startTime	endTime	Care Event Conversion
	11223306 Client Fake YSCINT - Yth Srvcs Crisis Int 42369.6354166667 42369.7083333333	11223306	Client	Fake	JRC Time	12/31/15 3:15 PM	12/31/15 5:00 PM	YSCINT - Yth Srvcs Crisis Int

<colgroup><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

In B2, I have the following formula:

=CONCATENATE(C2," ",E2," ",F2," ",N2," ",J2," ",K2)

And in A2, I have this formula:

=IFERROR(VLOOKUP(B2, CARE!$B$2:$B$394,1,FALSE), "No Match")

When I select J2 and K2, they appear like so in the formula bar:

12/31/2015 3:15:00 PM

12/31/2015 5:00:00 PM


This is spreadsheet #2 (CARE), which is being a turd.

A	B	C	D	E	M	N	O
Match?	CARE Key	Care ID	Last Name	First Name	Start Date/Time	End Date/Time	True Admit Reasons
	11223306 Client Fake YSCINT - Yth Srvcs Crisis Int 12/31/15 03:15 PM 12/31/15 05:00 PM	11223306	Client	Fake	12/31/15 03:15 PM	12/31/15 05:00 PM	YSCINT - Yth Srvcs Crisis Int

<colgroup><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

In B2, I have the following formula:

=CONCATENATE(C2," ",E2," ",F2," ",N2," ",J2," ",K2)

And in A2, I will have this formula:

=IFERROR(VLOOKUP(B2, YSCM!$B$2:$B$302,1,FALSE), "No Match")

When I select M2 and N2, they appear like so in the formula bar:

12/31/2015 03:15 PM

12/31/2015 05:00 PM

I have run this cross check before and it has worked beautifully, but in the past, the dates and times have always appeared in the CONCANTENATE cells as decimals in both spreadsheets.

I don't know if that helps you at all. Let me know if you need any more information.

 

[jakeman]

Ok, maybe the formula I gave you is only dealing exclusively with date, and forcing the hours and minutes portion to a generic time value. Great layout of your columns and requirements by the way...easy to follow.

I think this formula will solve your problem. In my previous post I gave you this:

[COLOR=#333333][I]=DATEVALUE(TEXT(D2,"MM/DD/YYYY HH:MM:SS"))[/I][/COLOR]

Try this instead:

=DATEVALUE(D2) + TIMEVALUE(D2)

With the above formula, it should now add both date and time together as the values appear in the cell.

If this works, please be sure to click on the "Like" link at the bottom right hand corner of my comment, so that some love can be spread around

 

[BrigitteR]

Oh my gosh! Thank you so much!! I'm sorry it took me so long to get back to you. The first of the month is busy for me, what with my start of the month reports and all, and then I got sick and had to take some time off. But I finally got back to this cross check today and I wanted to let you know that your new formula works!! HOORAY!

You are wonderful and I really appreciate your help. And thank you for the compliment about my columns and requirements. I tried to make it easy to follow. :D

 

[FDibbins]

2 notes on this...
1. You don't really need to use CONCATENATE to join cells, you can just use...
=C2&" "&E2&" "&F2&" "&N2&" "&J2&" "&K2

2. Not sure where the date/time is coming from, but a quick way to convert text date/time to real date/time is this...
- highlight the range you want
- select Data tab/Text2Columns
- Next/Next/check Date and select your format
- click OK