VBA Code to Compare Two String Entries with Time

[OilEconomist]

Hello and thanks in advance for your assistance. What is the VBA code to compare the following two entries.

(1) I want to make sure if the dates are the same. If so, proceed to the next part.
(2) If either entry is >9:30 AM, the I would like to reset that respective entry to 9:30 AM. e.g. .Cells(1, 1) would become .Cells(1, 1) = "2023-02-27, Mon @ 05:30 PM EST"
(3) Indicate which cell has the larger value.

.Cells(1, 1) = "2023-02-27, Mon @ 05:30 PM EST"
.Cells(1, 2) = "2023-02-27, Mon @ 08:30 AM EST"

Once again thanks!

 

[bebo021999]

This confuse me:
(2) If either entry is >9:30 AM, the I would like to reset that respective entry to 9:30 AM. e.g. .Cells(1, 1) would become .Cells(1, 1) = "2023-02-27, Mon @ 05:30 PM EST"

Do you mean:
would become .Cells(1, 1) = "2023-02-27, Mon @ 09:30 AM EST"
??
Also, how to indicate the larger cell? (highling background or font) ?

 

[OilEconomist]

@bebo021999 thanks not only for your response, but such a quick one. Apologies for the confusion. You are correct it would become Cells(1, 1) = "2023-02-27, Mon @ 09:30 AM EST".

I actually want to know which cell is larger than 9:30 AM so once the change is done I would know. Maybe bold it or both, if greater than 9:30 AM. Whichever one is or was greater, maybe just color fill the one with the greater value.

 

[bebo021999]

Are the cells actual dates formatted? or are they text string?

 

[OilEconomist]

Are the cells actual dates formatted? or are they text string?

They are string.

 

[Peter_SSs]

once the change is done I would know. Maybe bold it or both, if greater than 9:30 AM.

Is this what you mean?

23 02 28.xlsm
	A	B
1	2023-02-27, Mon @ 05:30 PM EST	2023-02-27, Mon @ 09:30 AM EST
2	2023-02-27, Mon @ 08:30 AM EST	2023-02-27, Mon @ 08:30 AM EST
After 9.30

Cell Formulas
Range	Formula
B1:B2	B1	=IF(TIMEVALUE(MID(A1,19,8))>TIME(9,30,0),LEFT(A1,18)&"09:30 AM EST",A1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B2	Expression	=TIMEVALUE(MID(A1,19,8))>TIME(9,30,0)	text	NO

 

[bebo021999]

VBA code;

Option Explicit
Sub findDate()
Dim rng As Range, cell As Range, dateA As Date, dateB As Date, timeB
Set rng = Range("A1:B1")
dateA = DateValue(Left(rng.Cells(1, 1), 10))
For Each cell In rng
    dateB = DateValue(Left(cell, 10))
    If dateB <> dateA Then Exit Sub
Next
For Each cell In rng
    timeB = Mid(cell, Len(cell) - 11, 8)
    If CDate(timeB) - 9 / 24 > 0 Then
        cell.Value = Replace(cell, timeB, " 09:00 AM")
        cell.Interior.Color = vbYellow
    End If
Next
End Sub

Before

after

 

[OilEconomist]

@Peter_SSs yes, but in VBA format. I think @bebo021999 has solved, but just needs to make a couple of small changes.

 

[OilEconomist]

VBA code;

Option Explicit
Sub findDate()
Dim rng As Range, cell As Range, dateA As Date, dateB As Date, timeB
Set rng = Range("A1:B1")
dateA = DateValue(Left(rng.Cells(1, 1), 10))
For Each cell In rng
    dateB = DateValue(Left(cell, 10))
    If dateB <> dateA Then Exit Sub
Next
For Each cell In rng
    timeB = Mid(cell, Len(cell) - 11, 8)
    If CDate(timeB) - 9 / 24 > 0 Then
        cell.Value = Replace(cell, timeB, " 09:00 AM")
        cell.Interior.Color = vbYellow
    End If
Next
End Sub

Before
View attachment 86387


afterView attachment 86388

@bebo021999 that works, but can you make a couple of changes to get to my exact needed answer. This way if somebody uses the final solution, they're not wondering why they're not getting the exact answer. I will mark as the final solution once done.

change

 cell.Value = Replace(cell, timeB, " 09:00 AM")

to

 cell.Value = Replace(cell, timeB, "09:30 AM")

basically remove the space before the 09 and change the 09:00 AM to 09:30 AM.