Problems with dates and times in Excel for plotting

[RogerL]

I have a text file that contains data from a monitoring station. When I import the file into Excel, I either get both the date and time in the same column and Excel is not recognizing it as a date/time so the only way to plot it on the x-axis is as text - but I need Excel to recognize it as a date and time so I can plot other data on the same plot. When I import from the text file and spearate the date (and use the DMY import wizard) on the date, I dont know how to combine date with the time for plotting (and get Excel to reecognize the time as a time) and add them together so I can plot as a date and time.

How does one get Excel to recognize dates and times as such? and how does one tell if Excel is seeing it as a date and time? also how does one combine a date and a time in separate columns so they can be plotted together? I continually have issues with dates and times in Excel so is that just my inexperience or a problem with the software?

Thank you for any help, I have attached a test file with two worksheets, one where the date and time are combined and one wher thy are separate. If possible, I would like both solutions if possible since data from various water monitoring devices often comes both ways (combined and separate). Or a link to any tutorial on this topic. I hav google searched and didnt find what I needed.

Thank you, Roger

[Richard Buttrey]

Hi,

All the values in column B in the testdata1 sheet are of course Text values. They need splitting into date & time numbers.
One way would be to use the Data TextToColumns functionality using the fixed length option, then convert what are still text values to numbers by entering a zero in a cell, copying it, then select the two columns and use Paste Special ADD.

ALternatively I'd just string slice the text
To get the date number use

Please Login or Register  to view this content.

and the Time number

Please Login or Register  to view this content.

The other test 2 sheet does already contain date & time numbers. YOU can always use =ISNUMBER(A1) or ISTEXT(A1) to determine whether a cell is a number or text.

[Pete_UK]

You could use this formula in E3 (say) to get the date:

=INT(SUBSTITUTE(B3,".","/")*1)

It will probably show a number of about 44,000 but you just need to format this as a date in the style you prefer.

To get the time part, you could use this formula in F3:

=RIGHT(B3,8)*1

This will initially give a fractional value, so you will need to format the cell as time. To get the date and time combined, just add those two values together, e.g. in G3:

=E3+F3

and format that cell in the style you prefer.

NOTE: As you use a different style for dates in the US compared with the UK, you may need to use the DATE function in E3 and separate out the parts for the year, month and day using LEFT and MID. Your example data only shows dates for 27th October, so I'm not sure how they would appear for single-digit days - do you have 01.10.2020 or 1.10.2020 ?

Hope this helps.

Pete

P.S. one way of checking if you have a real date/time is to format the cell as General - if it shows as a number and fraction (approx. 44000 if the date is recent) then Excel sees it as a date, otherwise it will be a text value

[MrShorty]

How does one get Excel to recognize dates and times as such?

The question that never seems to have a good, universal answer. Some of it is tied up in your OS's regional settings, some of it is related to differing text date format styles and such.

On the test2 tab. I added a date+time column in column E and entered the simple =SUM(B2:C2) in E2. Then I added a scatter chart to the spreadsheet and edited the data series (Select Data dialog) so that it had one data series that used column E as the X values and column D as the Y values.

I have observed that Excel has not yet been programmed to reliably recognize "." as date delimiters. I also notice that your text dates are DD/MM/YYYY rather than the US typical MM/DD/YYYY. On the testdata1 tab, I selected column B and did a find/replace command Find - "27.10." Replace with "10/27/" and Excel performed the text replacement and automatically converted to real date/time values.

[Kaper]

Edit: there are already some answers, but have a look also on this one:


You could just add date and time part.
Also if you have imported date and time column which only looks as date, but is a tekst (try to increase the width of the column - date shall be right justified, as any other number, text will be by default left justified) and using DAta Text to columns tools with DMY does not help you coud split it (by space as delimiter) into two DMY columns and then add it. It's what I did in first sheet.

See attached