14

Given say 2 different dates in A1 and B1, what I want to do is just show the range of dates as a label in A2 by using =concatenate(A1, " - ", A2)

However, A1 is in the default format (MM/DD/YYYY) and I want the label to have dates in the format DD MON, YYYY (as in 12/21/2012 -> 21st Dec, 2012).

Seems like this shouldn't be too complicated, but I couldn't find any way to do so by searching so far. I guess I could copy the same date into a different hidden cell with the appropriate format, and reference that for the concatenate function call - but I'd rather do so directly if possible.

So what I'm looking for is something like:

=concatenate(FORMAT(A1, 'DD MON YYYY'), " - ", FORMAT(A1, 'DD MON YYYY'))

except I can't find a suitable function.

12

There isn't a format function in Google spreadsheets. You have to use the TEXT(number, format) function.

I have to admit I always assume it will be format, but Excel uses Text so Google has to use it also.

19

This worked great for me:

=A1&TEXT(A2," mmmm d, yyyy")

Source

  • Welcome to Web Applications! Please don't post answers with just links. If the links becomes dead your answer is useless. Provide the actual answer in your post and if need be, link to the source. I suggest that you take some time to read the FAQ. Thanks! – codingbadger Aug 23 '12 at 7:19
  • Is there a list of mm dd yy type of formats Google Sheets supports? – Zlatty Apr 6 '16 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.