Question about Dates showing up as Numbers

[sspatriots]

Hi,

I have this formula that uses structured references to pull dates from another table into a different table. Both tables are formatted as dates. Was hoping someone could tell me why this lookup information wouldn't automatically come across as a date with everything preset to a date format.


Thanks, SS

=IF([@[G1
Job '#]]="","",

IF(ISBLANK(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),In_Production__2[#Data],MATCH("Material (Steel)",In_Production__2[#Headers],0),FALSE)),"",
IF(ISERROR(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),In_Production__2[#Data],MATCH("Material (Steel)",In_Production__2[#Headers],0),FALSE)),
IF(ISBLANK(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),Completed__2[#Data],MATCH("Material (Steel)",Completed__2[#Headers],0),FALSE)),"",
IF(ISERROR(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),Completed__2[#Data],MATCH("Material (Steel)",Completed__2[#Headers],0),FALSE)),
IF(ISBLANK(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),In_Production[#Data],MATCH("Material (Steel)",In_Production[#Headers],0),FALSE)),"",
IF(ISERROR(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),In_Production[#Data],MATCH("Material (Steel)",In_Production[#Headers],0),FALSE)),
IF(ISBLANK(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),Completed[#Data],MATCH("Material (Steel)",Completed[#Headers],0),FALSE)),"",
IF(ISERROR(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),Completed[#Data],MATCH("Material (Steel)",Completed[#Headers],0),FALSE)),

UPPER(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),Completed[#Data],MATCH("Material (Steel)",Completed[#Headers],0),FALSE)))),
UPPER(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),In_Production[#Data],MATCH("Material (Steel)",In_Production[#Headers],0),FALSE)))),
UPPER(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),Completed__2[#Data],MATCH("Material (Steel)",Completed__2[#Headers],0),FALSE)))),
UPPER(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),In_Production__2[#Data],MATCH("Material (Steel)",In_Production__2[#Headers],0),FALSE)))))

 

[Joe4]

Note that in Excel, dates really are just numbers with special date formats. Excel actually stores all dates as numbers, specifically the number of days since 1/0/1900.

Also note that formulas only return the underlying values of the cells that they are pulling from, they do NOT pull the formatting of those cells.

So if you want the results of the formulas to show in your typical date format, you just need to apply date formatting to those cells.

 

[sspatriots]

Note that in Excel, dates really are just numbers with special date formats. Excel actually stores all dates as numbers, specifically the number of days since 1/0/1900.

Also note that formulas only return the underlying values of the cells that they are pulling from, they do NOT pull the formatting of those cells.

So if you want the results of the formulas to show in your typical date format, you just need to apply date formatting to those cells.

I actually did apply date formatting to the destination cells where the lookup formulas are, but the dates still come out as 5 digit numbers. I know I can do a work around by adding the text formula for the date format to my lookup formulas, but its just weird that the formatting in the destination cell does not recognize my date format for that column of lookup formulas.

 

[Joe4]

I actually did apply date formatting to the destination cells where the lookup formulas are, but the dates still come out as 5 digit numbers. I know I can do a work around by adding the text formula for the date format to my lookup formulas, but its just weird that the formatting in the destination cell does not recognize my date format for that column of lookup formulas.

Can you please post the following?
1. The exact formula found in the offending cell
2. The value being returned by the formula
3. The format currently on that cell

 

[sspatriots]

Sure...

1. Exact formula is the one in the original post.
2. The value returned is "45105"
3. The format currently on the destination cell and the cell where the looked up information is coming from is in the attached image.

 

[Joe4]

I am guessing the issue is your formula is returning a text value instead of a numeric one.
Try adding a 0 to the end of your formula to coerce it to numeric, which then formatting will be applied to it, i.e.

=your current formula + 0

 

[sspatriots]

I am guessing the issue is your formula is returning a text value instead of a numeric one.
Try adding a 0 to the end of your formula to coerce it to numeric, which then formatting will be applied to it, i.e.

=your current formula + 0

Perfect. That did it. Thank you

 

[Joe4]

You are welcome.

Yes, formatting only works on numeric values, not text ones, so if the formula returns a "number entered as text" (and many formulas return values as text, by default), it needs to be coerced to a number in order for the formatting that can be applied to it.

There are are number of ways to coerce it to a number without changing its value like adding zero, multiplying by 1, etc.