Forum Discussion
Spill Formula converting dates to numbers, make it stop
I have two sets of data, one quite large. I need to match data from one sheet to a second sheet and return the matching results into a third sheet. The formula works fine, but it is converting dates into numbers. The column with the dates has multiple types of data so I can't just format the column to dates. How can I keep the dates as dates? The spill formula reads: =FILTER('Emp All'!A:AN,ISNUMBER(XMATCH('Emp All'!E:E,'1017-A'!D:D)))
Column with the dates ends up like this:
05200: 009.36 - this is correct
45388 - this should read 6-Apr-24
1 Reply
Even if 05200: 009.36 my be a date for you, Excel sees it as a text. On the other hand, 6-Apr-24 is recognized as a date and the FILTER function returns the date number. Select the spilled range and set the desired data format. Date numbers will be transformed to dates and text remain texts.
