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.
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.