Force Date Format

[xaikus506]

Hello, I am figuring out a way to make the column b of this worksheet be a Date Format. I tried using custom formatting but no good. You help is appreciated thanks!

 

[HongRu]

What is your column B' formula ?
Or column B only number in it without any formula, ex "=TEXT(XXX, 0)".

 

[xaikus506]

What is your column B' formula ?
Or column B only number in it without any formula, ex "=TEXT(XXX, 0)".

Hi, HongRu my formula for B is the spilled array from A6. I tried using text though I am not sure where to put it

 

[Fluff]

It's the Trim function that is causing the problem, as that returns text. Can you get rid of it?

 

[xaikus506]

It's the Trim function that is causing the problem, as that returns text. Can you get rid of it?

Hi I Tried it and it works Thank you!, By the way, I would like to ask if there a way to exclusively add a function to a specific column when dealing with spilled arrays like this like trim only the values in the spilled Array in A:A?

 

[Fluff]

Can you post the formula you are using, rather than just an image.

 

[xaikus506]

Hi Fluff my apoligies, this is the formula I am using:

Note: (this file and the connected file is placed in a SharePoint site I need to download it locally for the XL2bb addin to work:

Dashboard (1).xlsx
	A	B	C	D
6	Name of Analyst	Date of Process	Number of Errors	Subtasks
7	Azuelo, Chad	12/22/2021	1	CoReqGUO: 21-12-21
8	Azuelo, Chad	01/07/2022	1	CIBCChildVendor: 21-12-01
Dashboard (2)

Cell Formulas
Range	Formula
A6:D6494	A6	=IF(OR($B$2="",ISNUMBER(SEARCH("all",$B$2))),VSTACK({"Name of Analyst","Date of Process","Number of Errors","Subtasks"}, CHOOSECOLS(SORT(SORT(FILTER('Error Logs Consolidated'!B:H, (ISERROR(SEARCH("Analyst*",'Error Logs Consolidated'!D:D))* ('Error Logs Consolidated'!D:D<>0))* (ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B)))),2,1),3,1),{3,2,5,4})),"")
Dynamic array formulas.

and is connected to:

Dashboard (1).xlsx
	A	B	C	D	E	F	G	H
1	January 2022	0	01/00/1900	0	0	0	0	0
17	44585	Amora, Jhalifel	01/11/2022	Azuelo, Chad Marvin	BlackRockQ42021: 21-12-22	7	Done	ADe
26	44587	Amora, Jhalifel	01/13/2022	Azuelo, Chad Marvin	BlackRockQ42021: 21-12-22	1	Done	ADe
71	44613	Amora, Jhalifel	02/17/2022	Azuelo, Chad Marvin	BLK: EDy 220210	3	Done	ADe
84	44620	Amora, Jhalifel	02/24/2022	Azuelo, Chad Marvin	BLK: JMa 220216	2	Done	ADe
Error Logs Consolidated

Cell Formulas
Range	Formula
A1:H7053	A1	=VSTACK( TAKE(('C:\Users\JohnLesleeMacaldo\Downloads\[Consolidated Data.xlsx]ErrorLogs'!$A:$H),COUNTA('C:\Users\JohnLesleeMacaldo\Downloads\[Consolidated Data.xlsx]ErrorLogs'!$A:$A)), TAKE(('C:\Users\JohnLesleeMacaldo\Downloads\[Consolidated Data.xlsx]ErrorLogs2'!$A:$H),COUNTA('C:\Users\JohnLesleeMacaldo\Downloads\[Consolidated Data.xlsx]ErrorLogs2'!$A:$A)))
Dynamic array formulas.

Thanks for helping me

 

[Fluff]

Thanks for that, how about

=LET(x,IF(OR($B$2="",ISNUMBER(SEARCH("all",$B$2))),
CHOOSECOLS(FILTER('Error Logs Consolidated'!B:H,
(ISERROR(SEARCH("Analyst*",'Error Logs Consolidated'!D:D))*
('Error Logs Consolidated'!D:D<>0))*
(ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B)))),{3,2,5,4}),""),VSTACK({"Name of Analyst","Date of Process","Number of Errors","Subtasks"},SORT(HSTACK(TRIM(TAKE(x,,1)),DROP(x,,1)),{3,2})))

 

[jdellasala]

I only skimmed this, but didn't find any reference to DATEVALUE which uses Text to deliver a Date Serial Number as in

=DATEVALUE("12/15/22")

which delivers a value of 44910 which in Date format is12/15/2022.

Just sayin'!

 

[xaikus506]

Thanks for that, how about

=LET(x,IF(OR($B$2="",ISNUMBER(SEARCH("all",$B$2))),
CHOOSECOLS(FILTER('Error Logs Consolidated'!B:H,
(ISERROR(SEARCH("Analyst*",'Error Logs Consolidated'!D:D))*
('Error Logs Consolidated'!D:D<>0))*
(ISERROR(SEARCH("0",'Error Logs Consolidated'!B:B)))),{3,2,5,4}),""),VSTACK({"Name of Analyst","Date of Process","Number of Errors","Subtasks"},SORT(HSTACK(TRIM(TAKE(x,,1)),DROP(x,,1)),{3,2})))

Hi Fluff, I tried your formula it works like a charm, Thanks!. Though I have a question what if I want to add certain functions on a spilled array like Adding Trim in Column B,C and D as well? I tried modifying the Take and Drop Values by 2,3 & 4 and I got an Error.