Formula bar value vs displayed cell value

[Rackette]

Good rainy afternoon. Perfect time for doing computer stuff.
I export, in to excel, reports from a database.
In one of my columns, (start time), the cell value displays as 0001
However, what shows in the formula bar, for that same cell, is: 7/12/2019 12:01:00 AM
How do I get rid of the date and have both the cell value and the formula bar display the time as 0001?

I guess, I need it to get rid of the date and then convert to 24 hour time because I need to be able to make 'greater than' and 'less than' comparisons.
Also, when I convert to 24 hour time, I don't want the ":" in it. I just want it to be something like: 1330, 1420, 0900.

I have tried to change it myself, but none of the things I've tried get me to a time without the : in it.


-Christine

 

[Sam_D_Ben]

Something like this.

Excel 2013/2016
	A	B
1	Time	Convert
2	12-07-19 00:01:00	0001
Sheet3

Cell Formulas
Range	Formula
B2	=TEXT(A2,"HHMM")

 

[Rackette]

Could you maybe come up with a more complex answer, please?
Once again, I'm going to feel really dumb because I spent so much time trying to do what turns out to be a very simple thing.

Thank you!
-Christine

 

[MARK858]

I guess, I need it to get rid of the date and then convert to 24 hour time because I need to be able to make 'greater than' and 'less than' comparisons.

Why do you want to remove the date part from the cell when you can do it simply in your greater than formula?

Excel Workbook
	A	B	C
1	20/06/2019 08:00		Yes
2	18/06/2019 11:00
Sheet1

 

[Rackette]

Well, truly, it doesn't matter where it happens, I guess, but I don't know how to put an 'If' statement into VBA.
The whole story is that I need a macro for a dynamic range that will convert that date/time format so that I can have my macro look through the column and delete all rows where the time is prior to 0645.
I have this code, but it doesn't seem to reach down to the last row.
AND it doesn't delete the rows where the start time is prior to 0645.
AND it doesn't format the time to the HHMM format.

Sub AllBordersGetRange()
    Dim LstRw As Long, LstCo As Long
    LstRw = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    Range("H2:H" & LstRw - 1).Value = Range("D1:D" & LstRw).Value
End Sub

 

[MARK858]

I have this code, but it doesn't seem to reach down to the last row.

I can't see any reason why that code isn't finding the correct last row on the activesheet, easy to test with

Sub Testlastrow()
    Dim LstRw As Long
    LstRw = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    MsgBox "The last row with data is row number " & LstRw
End Sub

The -1 you have in your code means it would copy 1 row less than the last row.

AND it doesn't delete the rows where the start time is prior to 0645.
AND it doesn't format the time to the HHMM format.

Puzzled, there isn't anything in your code to delete anything (even if there was are you sure you want the line prior to the row deleted as you are starting the data in a different row in column H to column D) or change the number format

 

[MARK858]

Without further details try the code below if your date/times are in column D (please note that I have assumed that your date/times start in D2 and D1 is a header row).

Sub DeleteTime()
Dim LstRw As Long, i As Long
Application.ScreenUpdating = False
LstRw = Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row

For i = LstRw To 2 Step -1
  If Cells(i, "D") - Int(Cells(i, "D")) < TimeValue("06:45") Then Cells(i, "D").EntireRow.Delete
Next i

Range("D2:D" & LstRw).NumberFormat = "hhmm"
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Deleted - wrong post