# How can I re-format this date setting so I can use it for a histogram?

I have this date format which I'm struggling to readjust into a useable format for a FREQUENCY formula, for some reason it just can't read the dates right. Here is a sample of my data:

2020-02-13 00:44:27 2020-02-12 19:34:24 2020-02-12 18:43:34

How can I reformat this data so it works as D/M/YY?

edit retag close merge delete

Sort by » oldest newest most voted

You surely defined a sequence of bins (classes defined by their ascending limits) for FREQUNCY().

If your class limits are integer numbers as used for dates by default in spreadsheets, and the date-time-stamps (as exemplified) you have in a sequence also are numbers (not integers now) FREQUENCY() will work as defined. That's not a matter of formatting. If your "dates" or the limits of the bins aren't numbers but text, FREQUENCY() cannot work on them, again independent of any formatting.

To clear this: Select the column where the date-time-stamps are listed and make it very wide. Make sure that no alignment (left/right/centered) is set for the column. Any cell content or formula result being a number will now be displayed right-aligned while texts are left-aligned. (You can also use >View>Value Highlighting to show numbers in blue font color.) Do respectively with the column of the bins.

If you find that you tried to work with texts you need to convert them to the representation by numbers. This can be done with the tool >Data>Text to Columns....

more