Xlookup returns date code and won't cell format

[Rob_010101]

I'm having an issue where the following xlookup

=XLOOKUP(@A:A,PAYE!AC:AC,PAYE!F:F,"",0)&XLOOKUP(@A:A,Contract!W:W,Contract!F:F,"",0)

Returns the date as a code and refuses to format into the date format below.

I have manually typed the top code into a separate cell and formatted as date and it works, so it looks like the formula is what might be preventing it.

In previous versions of excel, on vlookup, going back and cell formatting the 'return array' as date would solve this but I have attempted this and still no luck.

Any help appreciated.

 

[etaf]

that looks like text
maybe try *1 or +0 at the end of formula to change back to a Number

 

[Rob_010101]

that looks like text
maybe try *1 or +0 at the end of formula to change back to a Number

=XLOOKUP(@A:A,PAYE!AC:AC,PAYE!F:F,"",1)&XLOOKUP(@A:A,Contract!W:W,Contract!F:F,"",1)

Returns

 

[etaf]

still looks like text and a different number
the date os a number from 1900 - so today is 45565
maybe post an xl2bb or share

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Rob_010101]

Test.xlsx
	A	B	C	D
1	Name	Type	Role	Start date
2	Test Person A	PAYE	Test role 1	41519
3	Test Person B	PAYE	Test role 2	43346
4	Test Person C	PAYE	Test role 3	42401
5	Test Person D	Contractor	Test role 4	43346
6	Test Person E	Contractor	Test role 5	45264
7	Test Person F	Sub-Contractor	Test role 6	45104
Live Staff List

Cell Formulas
Range	Formula
A2:A7	A2	=SORT(UNIQUE(VSTACK(FILTER(PAYE!$AC$2:$AC$1048576,PAYE!$AC$2:$AC$1048576<>""), VSTACK(FILTER(Contract!$W$2:$W$1048576,Contract!$W$2:$W$1048576<>"")))))
B2:B7	B2	=XLOOKUP(A:A,PAYE!$AC:$AC,PAYE!$C:$C,"",0)&XLOOKUP(A:A,Contract!$W:$W,Contract!$C:$C,"",0)
C2:C7	C2	=XLOOKUP(A:A,PAYE!$AC:$AC,PAYE!$G:$G,"",0)&XLOOKUP(A:A,Contract!$W:$W,Contract!$G:$G,"",0)
D2:D7	D2	=XLOOKUP(A:A,PAYE!$AC:$AC,PAYE!$F:$F,"",0)&XLOOKUP(A:A,Contract!$W:$W,Contract!$F:$F,"",0)
Dynamic array formulas.

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	8 digit ID	EE ID	Type	Forename	Surname	Start Date	Job title	Line manager	DOB	Contract type	Location	Working Days	Hours (including paid break)	Sex	Address 1	Address 2	Address 3	Address 4	County	Postcode	Personal email	Contact no. (work)	Contact no. (personal)	Emergency Contact Name	Emergency Contact Number	Current basic salary	Car allowance	Probation Period	Full name	FTE	1 FTE Salary	Length of service	Age	Probation review date	Extension months	Manager chased?	Status	Leaver?
2	00000010	10	PAYE	Test	Person A	02 September 2013	Test role 1	XXX	08/11/1974	Old		5	40	Male	1 Street	Village	Town		County	POST CD	XXX@XXX.com							3	Test Person A	1		11 Years, 0 Months & 28 Days	49	01 December 2013		Y	Pass
3	00000054	54	PAYE	Test	Person B	03 September 2018	Test role 2	XXX	05/04/1972	Old		5	40	Male	2 Street	Village	Town		County	POST CD	XX2@XXX.com							3	Test Person B	1		6 Years, 0 Months & 27 Days	52	02 December 2018		Y	Pass
4	00000063	63	PAYE	Test	Person C	01 February 2016	Test role 3	XXX	24/09/1976	Old		5	40	Male	3 Street	Village	Town		County	POST CD	XX3@xxx.com							3	Test Person C	1		8 Years, 7 Months & 29 Days	48	30 April 2016		Y	Pass
PAYE

Cell Formulas
Range	Formula
AC2:AC4	AC2	=IF(OR(ISBLANK(B2)), "", CONCATENATE(D2, " ", E2))
AD2:AD4	AD2	=IF(OR(ISBLANK(B2), ISBLANK(M2)), "", SUM(M2/40))
AE2:AE4	AE2	=IF(OR(ISBLANK(B2), ISBLANK(Z2)), "", SUM(Z2/AD2)*1)
AF2:AF4	AF2	=IFERROR(IF(OR(ISBLANK(D2),ISBLANK(F2)),"",DATEDIF(F2,TODAY(),"Y")&" Years, "&DATEDIF(F2,TODAY(),"YM")&" Months & "&DATEDIF(F2,TODAY(),"MD")&" Days"), "Not started")
AG2:AG4	AG2	=IF(OR(ISBLANK(I2)), "", DATEDIF(I2, TODAY(), "Y"))
AH2:AH4	AH2	=IF(OR(ISBLANK(B2), ISBLANK(AB2)), "", EDATE(F2,AB2+AI2)-1)
A2:A4	A2	=IF(OR(ISBLANK(B2)),"",REPT(0,8-LEN(B2))&B2)
C2:C4	C2	=IF(OR(ISBLANK(B2)), "", "PAYE")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B4	Cell Value	duplicates	text	NO
AK1:AL4	Cell Value	contains "Pending"	text	NO
AK1:AL4	Cell Value	contains "Pass"	text	NO
AF1:AG4	Cell Value	contains "Not started"	text	NO
A1:B1	Cell Value	duplicates	text	NO

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	8 digit ID	EE ID	Type	Forename	Surname	Start date	Service provided	Line manager	DOB	Location	Supplier Competence Review Period	Address 1	Address 2	Address 3	Address 4	County	Postcode	Contact no. (Amaro)	Contact no. (personal)	Personal email	Emergency Contact Name	Emergency Contact Number	Full name	Length of contracted service	Age
2	00000C22	C22	Contractor	Test	Person D	03 September 2018	Test role 4	XXX			3	4 Street	Village	Town		County	POST CD						Test Person D	6 Years, 0 Months & 27 Days
3	00000C31	C31	Contractor	Test	Person E	04 December 2023	Test role 5	XXX			3	5 Street	Village	Town		County	POST CD						Test Person E	0 Years, 9 Months & 26 Days
4	000000C3	C3	Sub-Contractor	Test	Person F	27 June 2023	Test role 6	XXX			3	6 Street	Village	Town		County	POST CD						Test Person F	1 Years, 3 Months & 3 Days
Contract

Cell Formulas
Range	Formula
W2:W4	W2	=IF(OR(ISBLANK(D2), ISBLANK(E2)), "", CONCATENATE(D2, " ", E2))
X2:X4	X2	=IFERROR(IF(OR(ISBLANK(D2),ISBLANK(F2)),"",DATEDIF(F2,TODAY(),"Y")&" Years, "&DATEDIF(F2,TODAY(),"YM")&" Months & "&DATEDIF(F2,TODAY(),"MD")&" Days"), "Not started")
Y2:Y4	Y2	=IF(OR(ISBLANK(I2)), "", DATEDIF(I2, TODAY(), "Y"))
A2:A4	A2	=IF(OR(ISBLANK(B2)), "", REPT(0,8-LEN(B2))&B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X1:Y2,W2,W3:Y4,K1:K4	Cell Value	contains "Not started"	text	NO
A1:B1	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
K2:K4	List	1,2,3,4,5,6
C2:C4	List	Contractor, Sub-Contractor, Agency

 

[etaf]

????
you need to select the area of excel for XL2BB to use

 

[Rob_010101]

????
you need to select the area of excel for XL2BB to use

Sorry, done

 

[etaf]

i think XL2BB has already converted the text numbers to values

but i'm not sure how they all link together
a simple sheet would help I think
or maybe add to a share
and then the text format may retain

I suspect on the test.xls - its column D

which uses a concatenate
=XLOOKUP(A:A,PAYE!$AC:$AC,PAYE!$F:$F,"",0)&XLOOKUP(A:A,Contract!$W:$W,Contract!$F:$F,"",0)
which may result in text and NOT a date

 

[Rob_010101]

i think XL2BB has already converted the text numbers to values

but i'm not sure how they all link together
a simple sheet would help I think
or maybe add to a share
and then the text format may retain

I suspect on the test.xls - its column D

which uses a concatenate
=XLOOKUP(A:A,PAYE!$AC:$AC,PAYE!$F:$F,"",0)&XLOOKUP(A:A,Contract!$W:$W,Contract!$F:$F,"",0)
which may result in text and NOT a date

It looks like you're right and because of the concatenation, it returns as text

I've fixed this by using the following instead

=IF(B2="PAYE", XLOOKUP(@A:A, PAYE!AC:AC, PAYE!F:F, "", 0), IF(B2="Contractor", XLOOKUP(@A:A, Contract!W:W, Contract!F:F, "", 0), IF(B2="Sub-Contractor", XLOOKUP(@A:A, Contract!W:W, Contract!F:F, "", 0))))

Thank you

 

[etaf]

you are welcome, glad to have pointed you in the right direction

As i mentioned you can turn TEXT numbers back to real numbers by using
*1
or +0
or i think
-- also will do that at the start of the formula
in the formula or at the end

but as you have fixed it, aqll sorted