# Force Date Format



## xaikus506 (Dec 14, 2022)

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 (Dec 14, 2022)

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


----------



## xaikus506 (Dec 14, 2022)

HongRu said:


> 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 (Dec 14, 2022)

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


----------



## xaikus506 (Dec 14, 2022)

Fluff said:


> 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 (Dec 14, 2022)

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


----------



## xaikus506 (Dec 14, 2022)

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).xlsxABCD6Name of AnalystDate of ProcessNumber of ErrorsSubtasks7 Azuelo, Chad12/22/20211CoReqGUO: 21-12-218 Azuelo, Chad01/07/20221CIBCChildVendor: 21-12-01Dashboard (2)Cell FormulasRangeFormulaA6:D6494A6=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).xlsxABCDEFGH1January 2022001/00/1900000001744585Amora, Jhalifel01/11/2022Azuelo, Chad MarvinBlackRockQ42021: 21-12-227DoneADe2644587Amora, Jhalifel01/13/2022Azuelo, Chad MarvinBlackRockQ42021: 21-12-221Done ADe7144613Amora, Jhalifel02/17/2022Azuelo, Chad MarvinBLK: EDy 2202103Done ADe8444620Amora, Jhalifel02/24/2022Azuelo, Chad MarvinBLK: JMa 2202162Done ADeError Logs ConsolidatedCell FormulasRangeFormulaA1:H7053A1=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 (Dec 15, 2022)

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 (Dec 15, 2022)

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 is*12/15/2022*.

Just sayin'!


----------



## xaikus506 (Dec 15, 2022)

Fluff said:


> Thanks for that, how about
> 
> 
> 
> ...


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.


----------



## xaikus506 (Dec 14, 2022)

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!


----------



## Fluff (Dec 16, 2022)

You cannot run trim on the date otherwise you end up where you started.


----------



## xaikus506 (Dec 18, 2022)

Thank you Fluff for the assistance  🙂


----------



## Fluff (Dec 19, 2022)

Glad to help & thanks for the feedback.


----------

