CODEWITHSUNDEEP
dategoogle-sheetsnumbers
Kyle O'Hara
Kyle O'Hara

Reputation: 11

Google Sheets converting dates to numbers

I've run into a problem and haven't been able to find a solution for this via Google.

Using Google Sheets, I have a series of dates in the DD/MM/YYYY format in a column, and I'm filtering them based on info in another column with this formula in column G:

=ARRAYFORMULA(FILTER(D:D, B:B=B2))

where the dates are in column D. For example, 5/20/2013.

This works fine, but next, I want to add a string to the front of the dates like so:

=ARRAYFORMULA("End: "&FILTER(D:D, B:B=B2))

The dates are converting into numbers. So 5/20/2013 becomes End: 41414 instead of End: 5/20/2013, which is what I need.

Further, this happens whenever I try to reference a cell with a date in it in any formula. I have another column that puts together some values from other columns like this:

=IF(B:B=B2,ARRAYFORMULA(F2&" "&E2),"")

I want to add the date to that, like so:

=IF(B:B=B2,ARRAYFORMULA(F2&" "&E2&" "&G2),"")

The result I'm getting is the same, where I should see 5/20/2013 I'm instead getting 41414.

Can anyone tell me what's going on and how I can prevent it from converting my dates to numbers like that? Thanks!

Upvotes: 0

Views: 5705

Answers (2)

JPV
JPV

Reputation: 27242

In addition to Chris' answer: just converting to text should also work:

=ARRAYFORMULA("End: "&FILTER(to_text(D:D), B:B=B2))

Upvotes: 1

Chris Hick
Chris Hick

Reputation: 3094

You can format the formula output using TEXT, for example:

=ARRAYFORMULA("End: "&FILTER(TEXT(D:D,"mm/dd/yyyy"), B:B=B2))

=IF(B:B=B2,ARRAYFORMULA(F2&" "&E2&" "&TEXT(G2,"mm/dd/yyyy")),"")

Upvotes: 2

Related Questions