0

I have been lurking for a while and trying to find a solution, but to no avail.

So the problem is that i have a column with the following data format of date and time. It's custom and exported from some hardware.

27.08.2024 08:20:19

(DD.MM.YYYY hh:mm:ss) (thank the commenter for noticing that this was not in code)

Excel doesn't recognise this type of formatting, therefore on chart's x-axis it keeps it intact as is. Also, Excel does recognise this as Text i suppose, because when converting to Number nothing happens (actually nothing happens even with any other format).

What i noticed: manually deleting the spacebar between date and time, and then typing a spacebar back makes it digestible for formatting. In fact all the formats work properly from that point on. (by keys: select cell -> F2 -> Backspace the Space -> Type the space -> Enter = formatting works...)

step 1->27.08.2024 08:19:59

step 2->27.08.202408:19:59

step 3->27.08.2024 08:19:59

TL:DR Here. Problem is that there are up to 10th of thousands of rows with weird format, that excel understands only as text. Manually deleting spacebar and then typing it back does the magic, but this is not the option. I tested a VBA. End goal: to make a chart show only time without the date, while leaving the data intact (without deleting the date from the cells or using a delimiter (this can change if there is no other option)).

Sub test()

Dim nLastRow, i As Integer
Dim wbCurrent As Workbook
Dim wsCurrent As Worksheet

      Set wbCurrent = ActiveWorkbook            
      Set wsCurrent = wbCurrent.ActiveSheet
      i = 1
      'the Last row index
      nLastRow = Cells(Rows.Count, i).End(xlUp).Row
      'putting a space after date, because that somehow fixes the stupid formatting.
      For i = 1 To nLastRow Step 1
      If InStr(1, wsCurrent.Cells(i, 1).Value, " ", vbTextCompare) > 0 Then
            wsCurrent.Cells(i, 1) = Replace(Cells(i, 1).Value, " ", " ", 1, 1)
      End If
      Next i
End Sub

It does the job of replacing the spacebar with a spacebar, but doesn't have the same effect as with manual. Maybe i am missing something like instead of vbTextCompare it should do vbBinaryCompare.

No difference between MacOS and Windows.

Thanks.

UPD. Resolved.

It required some fiddling with the local time and writing standards. So the formula in excel looks like this now. instead of , it uses ; instead of DD/MM/YYYY it is German TT/MM/JJJJ

=DATEVALUE(TEXT(SUBSTITUTE(LEFT(A2;10);".";"/");"TT/MM/JJJJ")) + TIMEVALUE(RIGHT(A2;8))

Now this is the part of code that i implemented.

With ActiveSheet
'[-Block-Start-----Block to separate Date and Time from the Column 1--------]
     RowIndex = 1
     LastRowIndex = Cells(Rows.Count, RowIndex).End(xlUp).Row
     
     Range("B2").EntireColumn.Insert Shift:=xlToRight
     Range("B1").Value = "Time"
     RefCellAddress = .Cells(2, 1).Address(False, False, xlR1C1, False, RelativeTo:=Cells(2, 2))
     .Cells(2, 2).Formula = "=DATEVALUE(TEXT(SUBSTITUTE(LEFT(" & RefCellAddress & ",10),""."",""/""),""TT/MM/JJJJ"")) + TIMEVALUE(RIGHT(" & RefCellAddress & ",8))"
'the original formula =DATEVALUE(TEXT(SUBSTITUTE(LEFT(A2;10);".";"/");"DD/MM/YYYY")) + TIMEVALUE(RIGHT(A2;8))
'Spread the formula to the whole column
     Set FormulaCell = .Cells(2, 2)
     .Range(Cells(LastRowIndex, FormulaCell.column), Cells(FormulaCell.Row, FormulaCell.column)).FillDown
'[-Block-End-------------]
'rest of code there...

It's ugly but... hey, it works! Thanks everyone!

Improve this question
4
  • 1
    Perhaps this space between the date and time is a hard space with code 160. Then you can replace it in bulk with a regular space code 32. Commented Sep 19, 2024 at 11:52
  • Did you manually type the example into your question? If you did, please copy/paste the formula bar contents of a misbehaving cell from your worksheet to your question. Then we might be able to tell what is really there. Commented Sep 19, 2024 at 17:59
  • @MGonet i was suspecting this as well, but i don't know how to check this, nor how to do it via VBA (to write a particular space with code 32). Commented Sep 20, 2024 at 9:27
  • Simple space is " " (space bar) or Chr(32), hard space is Chr(160). Commented Sep 20, 2024 at 10:00

2 Answers 2

Reset to default
1

You could always add a column and use a big old text parsing formula haha. Where A1 is the input cell:

= DATEVALUE(TEXT(SUBSTITUTE(LEFT(A1,10),".","/"),"DD/MM/YYYY")) + TIMEVALUE(RIGHT(A1,8))

This would give you a full datetime, and can be formatted to any date format, including custom format strings like "DD/MM/YYYY hh:mm:ss". The formula uses:

  • LEFT(x,10) and RIGHT(x,8) to read the date and time individually.
  • SUBSTITUTE(x, ".", "/") to swap the date delimiter to something Excel prefers.
  • DATEVALUE(TEXT(x,"DD/MM/YYYY")) to parse the date text into Excel's date format, and force the locale format.
  • TIMEVALUE(x) to parse the time text into Excel's time format.

So for your purposes, you can have times in Excel's format in a column with this formula:

=TIMEVALUE(RIGHT(A1,8))

Which you can copy over the original column (paste values) or leave separate, and then process into a chart.

Improve this answer
5
  • Yeah, seems like there is no other way other than using a delimiter. Albeit i'd rather need it in VBA code. Thanks anyway for the answer. Commented Sep 19, 2024 at 11:26
  • Ah yeah fair, I didn't know what you had meant by "delimiter" there. I'm one of the many who skipped VBA to be able to use JavaScript on Google Sheets so this is just from more of that kinda skillset. Commented Sep 19, 2024 at 12:31
  • = DATEVALUE(TEXT(SUBSTITUTE(LEFT(A1,10),".","/"),"DD/MM/YYYY")) + TIMEVALUE(RIGHT(A1,8)) This seem to have some issue. I typed it by hand as well, still didn't work. Excel gives an error as if there is some typo or smth is missing. Commented Sep 19, 2024 at 13:24
  • I've tested it out from both your quote of it and my original and it seems fine; it maps "27.08.2024 08:19:59" to 45531.34721 (which can then be date/time–formatted). The first time I checked it, I pasted it into cell A1 and so triggered a circular reference error, since the formula expects the data to be in A1 so has to itself be elsewhere. Commented Sep 19, 2024 at 16:38
  • I do understand that how it works, yes, but somehow the Excel itself stumbled. I already fixed it btw. Apparently there were several not-so-obvious obstacles: a) instead of , it required ; as a separator within formula b) instead of DD/MM/YYYY it was meant to be TT/MM/JJJJ. Cause the OS is in german apparently... It works now, thanks! Commented Sep 20, 2024 at 9:20
0

If you have 365 you can use the following. Note how you can add multiple delimiters without having to use nested substitutes:

=LET(
    d, TEXTSPLIT(
        A1,
        MAKEARRAY(
            3,
            1,
            LAMBDA(r, c, CHOOSE(r, CHAR(32), CHAR(160), CHAR(46)))
        )
    ),
    DATE(INDEX(d, 3), INDEX(d, 2), INDEX(d, 1)) + INDEX(d, 4)
)
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.