Flash Fill Question

[Glasgowsmile]

Good morning,

Currently, I'm trying to compare a date with another cell that has a date in it but it's formatted in a way that conditional formatting isn't working.

For example:
A1 = 6/11/2021
O1 = 17May2021 MON

I'm trying to figure out how to extract that 17May2021 into another cell as a date so I can flash fill it down the spreadsheet and then compare A1 <> O1 essentially.

 

[arthurbr]

Perhaps

=0+TEXT(LEFT(A1,FIND(" ",A1)),"dd/mm/yyyy")

 

[JoeMo]

Assuming the format of O1 is always "ddmmmyyyy" try:

=DATEVALUE(LEFT(O1,9))
and format that as a date .

 

[Glasgowsmile]

Perhaps

=0+TEXT(LEFT(A1,FIND(" ",A1)),"dd/mm/yyyy")

Thanks, that came back with a value error for some reason but I did find a way to get it down but now I'm trying to use VBA to add the date into the cell and I'm having an error

Keeps saying "Expected )" for the Formula line.

ws.Range("W36).Formula = "=DATE(AB36,AA36,Z36)"
ws.Range("W36").Select
Selection.AutoFill Destination:=Range("W36:V500")

 

[Glasgowsmile]

Assuming the format of O1 is always "ddmmmyyyy" try:

=DATEVALUE(LEFT(O1,9))
and format that as a date .

That was a quick and easy fix, thanks!

Same issue with the above regarding the VBA though.

ws.Range("W36).Formula = "=DATEVALUE(LEFT(O36,9))"
ws.Range("W36").Select
Selection.AutoFill Destination:=Range("W36:V500")

Disregard! I found that I was missing an " around W36.

Thanks for the help!

 

[JoeMo]

That was a quick and easy fix, thanks!

Same issue with the above regarding the VBA though.

ws.Range("W36).Formula = "=DATEVALUE(LEFT(O36,9))"
ws.Range("W36").Select
Selection.AutoFill Destination:=Range("W36:V500")

Disregard! I found that I was missing an " around W36.

Thanks for the help!

You are welcome - thanks for the reply.