0

I've been sent a spreadsheet with dates in the format dd/mm/yyyy hh:mm:ss. The date column itself is formatted using the Custom option in the Number tab, and the option selected is dd/mm/yyyy hh:mm so that the entry in the actual cell is 29/05/2017 14:52 with the seconds omitted. This is fine.

I've then pulled my own version of the report spreadsheet directly from the application that generates it, and the date column is formatted as text and displays 29/05/2017 14:52:12 etc. I've tried to duplicate the formatting from the 1st report, selected Custom dd/mm/yyyy hh:mm , but no matter what I do, the column's cells will not convert to dates.

After making the change I've gone back into the Format menu and seen that the selected option is still highlighted, however the cells will not behave like dates - can't filter as dates etc. What am I doing wrong?

Branoic
  • 1

1 Answers1

0

After making the change I've gone back into the Format menu and seen that the selected option is still highlighted, however the cells will not behave like dates - can't filter as dates etc.

Excel only knows three types of data: text, number and boolean (true / false)

  • Numbers can be integers and decimals and you can format them to appear as several type of information: numbers with different formattings, currency, date, time ...
  • text is just text, whichever number formatting you try to apply on it Excel will just ignore it as it will apply number formatting to numbers only.

What am I doing wrong?

Instead of trying to solve it with formatting, you need to use another column where you use a formula to convert your data to numbers (formatted as dates).
Have a look on DateValue function.

After converting your text to numbers you can apply your desired date formatting on it.

Máté Juhász
  • 21,451
  • thanks very much, that worked. I noticed though that the converted date value strips out the time element hh:mm:ss. For my purposes this is fine, as all I needed was the date and not the time. However its curious because the original spreadsheet I received with the column already formatted as dates had the time elements still intact, and I don't believe the person who sent me the report would have used DateValue, or any formula for that matter. I wonder how they managed to do it. – Branoic May 29 '17 at 09:51
  • Without even knowing the data it's pretty difficult to tell, how somebody else has converted it. Why don't you ask her / him? – Máté Juhász May 29 '17 at 10:00
  • New job. Would prefer not to ask too many questions in the office :-) thanks again for the help – Branoic May 29 '17 at 10:50
  • Asking never hurts. Now you know one way, can show them and ask whether they use the same. There can even be a setting in the source application with different date format. – Máté Juhász May 29 '17 at 10:52