Ask Your Question

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

asked 2020-02-13 15:30:14 +0100

garethgcraven gravatar image

updated 2020-08-07 13:34:22 +0100

Alex Kemp gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-02-13 16:15:06 +0100

Lupp gravatar image

updated 2020-02-13 16:21:08 +0100

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....

edit flag offensive delete link more


Got it working now thanks to your help, thank you!

garethgcraven gravatar imagegarethgcraven ( 2020-02-14 13:02:37 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-02-13 15:30:14 +0100

Seen: 37 times

Last updated: Feb 13 '20