XLOOKUP & INT

[Jamiek1]

Hi

I am trying to return a date using XLOOKUP which it does, however, the original date is shown as Date and Time 01/02/2023 12:40:58 and I want to remove the time without changing the format of the cell as when I then use a pivot on the returned date it will still show as date and time.

=XLOOKUP (J2,A:A,B:B,"",0)
How do I incorporate the INT on the returned data in the same formula as I don't want a new column of data.

 

[awoohaw]

I am a little confused, do you want to return and show only the date portion (but still have the time portion in the value) but not formatted as solely a date? Then display date and time in your pivot charts?

If you use INT in the match function you will strip out the time component.

Have you considered formatting the pivot table data as date/time?

It will help greatly if you can post a mini workbook with a sample of your data, the calculation, and how it is used in the pivot table? the xl2bb add in (link below) enables you to share the data with the forum. If there is sensitive or personal data, you can anonymize it.

 

[Jamiek1]

Apologies I should have explained this better.

Column A and J have unique ID's
Column B has Date and Time

I want to return just the date from Colum B into column K

 

[awoohaw]

Okay, here are two different applications of the INT function. I don't know which is best from a performance perspective:

Book1
	A	B							I	J	K	L
1	ID	Date/Time								ID	XLOOKUP INT(B:B)	INT(XLOOKUP())
2	PLH	22-Feb-2023 10:05:07								OEP	10-Feb-2023 00:00:00	10-Feb-2023 00:00:00
3	SCV	20-Mar-2023 08:07:15								FTI	17-Feb-2023 00:00:00	17-Feb-2023 00:00:00
4	OEP	10-Feb-2023 09:13:38								KLO	08-Feb-2023 00:00:00	08-Feb-2023 00:00:00
5	FTI	17-Feb-2023 04:56:26								UHC	08-Mar-2023 00:00:00	08-Mar-2023 00:00:00
6	KLO	08-Feb-2023 22:38:33
7	UHC	08-Mar-2023 05:13:21
8	DUE	11-Feb-2023 06:59:54
9	LCE	04-Feb-2023 07:51:36
10	ZFQ	03-Mar-2023 02:33:19
11	MUP	10-Mar-2023 20:11:16
12	SRW	18-Mar-2023 12:29:55
13	CRG	17-Feb-2023 07:25:09
14	NDP	04-Feb-2023 20:33:46
15	MMD	21-Mar-2023 11:32:39
16	ICQ	18-Feb-2023 09:06:30
Sheet1

Cell Formulas
Range	Formula
K2:K5	K2	=XLOOKUP(J2,A:A,INT(B:B),"",0)
L2:L5	L2	=INT(XLOOKUP(J2,A:A,B:B,"",0))

 

[Jamiek1]

Perfect thanks very much.
Large data set and will check performance but option 1 appears to be good so far.

 

[awoohaw]

My pleasure, happy to help.
I formatted the columns to show the entire time component, please format as your requirements dictate.

And welcome to the forum!