Gfrktyl

I am pulling values from one sheet to another. Sheet 1 has ticket ID and Sheet 2 has various values associated with ticket ID. I am finding the matching ticket ID on Sheet 2 and pulling the multiple values associated with that ticket ID onto Sheet 1.

The Problem

Sheet 2 contains some values that are in date and time format ( YYYY/MM/DD HH:MM:SS ). The formula is returning the date-time value as a number, like 42010.4, rather than the original value.

How do I get the date-time values to display in their original format?

The specifics of how I'm pulling the values aren't relevant to my question. But just for context:

I am using the MATCH function to find the correct row on Sheet 2 and storing that in column C of Sheet 1 for each ticket ID.

That result is then used to populate a number of cells in the same row with values from Sheet 2. For example, the value from Sheet 2 column B is populated using a formula similar to this:

=IF($C2>0,INDIRECT("'Sheet2'!B"&C2),"")

The above formula works and is returning values from the appropriate cells. However, it isn't a simple cell reference. Rather, it is a somewhat complex formula and it isn't clear how to get the result to display like the source.

This questioned was spawned by another question (dealing with the task described at the beginning of this one). The accepted answer to that one offers two types of solutions, one where the values from the second sheet are pulled into separate cells, and one where all of the values from the second sheet are concatenated into a single cell. Some of the cells on the second sheet contain date-times, which presents a problem for both approaches, and the fix is different for the two cases. I'll describe both.

What is happening

The stored date-time is being copied in its "raw", unformatted form. For example, Jan 6, 2015 8:55:08 AM is stored as 42010.3716 because it is the 42,010th day since 12/31/1899 (the standard reference date), and 8:55:08 AM is 37.16% of a day. The date-time is stored in the cell as a single number in that form and then Excel derives the year, month, day, and time information from it as needed to display the value in the desired format.

Values copied into separate cells

This is the simplest to fix. In this case, seeing the raw date-time is actually a good thing because it means that the date-time information has been preserved and can be used (you can use the value to do date-time testing, date-time arithmetic, etc.). You just need to format it.

One way is to copy the formatting from the original cell. Use copy, then in the new cell, paste-special format-only. You can access this by right-clicking the cells and using the context menues, or from the ribbon menu at the top.

The other way, particularly if you want to use a slightly different format for the copied value, is to use a custom format. For the new cell, access the cell format dialog by either right-clicking the cell or from the ribbon menu. Select Custom Format from the list of format types. You can either pick something close as a starting point from the list of special formats presented, or just enter the codes you want in the edit window.

The complete list of format codes can be found here. To duplicate the format described in the question, the code would be:

Yyyy/Mm/Dd Hh:mm:ss

The cell will then appear like the original.

Values concatenated into a single cell

In this case, the embedded value is already treated as text, so it has lost its value as a usable date-time. It is just a question of having it appear as a readable date-time in the string. This can be done by using the TEXT function to format the value while the string is being constructed. I'll borrow some code from the other question as the basis for this solution:

=IF($C2>0,INDIRECT("'tickets_info'!B" & $C2)

  & " " & INDIRECT("'tickets_info'!C" & $C2)

  ...

  & " " & INDIRECT("'tickets_info'!N" & $C2),"")

Here, C2 is a lookup value to find the matching row and the &'s are used to concatenate the various source cells into a single string containing all of the information for the matching ticket. Let's say column C in tickets_info is a date-time. When this string is created, that position in the string will show the raw date-time value instead of the formatted value displayed in tickets_info.

You can use the TEXT function to format that value. The TEXT function uses codes very similar to the custom format codes for the cell. The codes and a mini-tutorial can be found here. For the format described in the question, the result would look like this:

=IF($C2>0,INDIRECT("'tickets_info'!B" & $C2)

  & " " & TEXT(INDIRECT("'tickets_info'!C" & $C2),"yyyy/mm/dd hh:mm:ss")

  ...

  & " " & INDIRECT("'tickets_info'!N" & $C2),"")

The TEXT function is just applied to the derived cell reference (the result of the INDIRECT function).