0

I have a cell that contains "01.08.2014", which is a date. I want to use a formula to turn it into "01.08.2014 00:00:00" which is supposed to be date and time.

0

Assuming your date "01.08.2014" is in cell A1, this should work.

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

The DATE() function works by pulling out the Year, Month and Date components of your string and reassembling them into a proper Excel date.

| improve this answer | |
  • Somehow LEFT(A1;2), which should give "01", returns "41" instead, so it appears my data is formatted backwards and displayed differently.. – Hans Sep 12 '14 at 8:33
  • @Hans You have a ; instead of ,? – DavidPostill Sep 12 '14 at 8:36
  • Yes, I got an error using , Also, I just found out, somehow the content of the cell which displays 01.08.214 is actually 41852. What type of format is that? Seconds? To make that more clear: =A1 &"test" yields 41852test – Hans Sep 12 '14 at 8:38
  • 41852 is an unformatted date in Excel. As you've discovered, you can use the built-in cell formatting in this case - the formula I explain above is for dates embedded as text. And to explain the comma/semi-colon thing, in some European versions of Excel, ; is used instead of ,. – Andi Mohr Sep 12 '14 at 10:34
0

I fixed it as follows: Apparently, the date was already fully included in the information in the cell, so I only had to reformat: Select all cells, press CTRL+1 and then define your own format, e.g. I did dd.mm.yyyy hh:mm:ss

| improve this answer | |
0

Date/Time values are stored as a single number. The integer portion is the date (number of days since a reference date), and the decimal portion is the time (fraction of a day). That is the actual content of the cell if Excel stores it as date/time data. What you see displayed is just formatting to express it in human-readable form. Using string functions to parse the date components won't work because that is not what is actually in the cell.

You can format the contents any way you want. Custom formats give you a lot of flexibility. Or you can use functions to extract components of the date and time and concatenate them for a custom format. This link provides some explanation: http://www.excel-examples.com/microsoft-excel-date-time-functions.htm

EDIT: @Hans, When I wrote this, I missed your post that you had already solved your problem. I'll leave the answer in case it is useful to anyone else.

| improve this answer | |

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.