Formula bar value vs displayed cell value

Rackette

New Member
Joined
Jul 2, 2019
Messages
37
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Something like this.


Excel 2013/2016
AB
1TimeConvert
212-07-19 00:01:000001
Sheet3
Cell Formulas
RangeFormula
B2=TEXT(A2,"HHMM")
 
Upvote 0
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
 
Upvote 0
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
ABC
120/06/2019 08:00Yes
218/06/2019 11:00
Sheet1
 
Upvote 0
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.
Code:
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
 
Upvote 0
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
Code:
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 :confused:
 
Upvote 0
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).

Code:
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
 
Upvote 0
Deleted - wrong post
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top