VBA inputbox Greater Than or Equal to for Date, UTC Date Time Now Format

[vbanewbie68]

Hello

Please see my screenshots below.

I enter a date, using this greater than or equal to this 2021-12-07T14:03:36 UTC. I get blank cells.

I am not sure what went wrong with the codes?

The data can be either big or small. I get it weekly data.

Hope the everything makes sense?

Thank you

Regards

Vbanewbie68

 

[Dan_W]

The contents of column G are not actually dates. Excel is treating them like text because it doesn't recognise the UTC date/time format (as you've used it). If you delete from the T onwards in each cell, leaving just the date, it'll probably start working the way you expect it to.

 

[Dan_W]

You can keep the time too, if you want to, you just need to reformat the data slightly.

 

[vbanewbie68]

The contents of column G are not actually dates. Excel is treating them like text because it doesn't recognise the UTC date/time format (as you've used it). If you delete from the T onwards in each cell, leaving just the date, it'll probably start working the way you expect it to.

Hi Dan

Thank you for your time on this. See the screenshot below.

I managed to convert using Text to Columns Wizard to delete T onwards as see the result below. Would it be good to get VBA to delete T onwards but not sure how.

The result after I entered greater than or equal this 07/12/2001 it does not work as see below the screenshot.

 

[vbanewbie68]

Hi Dan

Thank you for your time on this. See the screenshot below.

I managed to convert using Text to Columns Wizard to delete T onwards as see the result below. Would it be good to get VBA to delete T onwards but not sure how.

View attachment 69907


The result after I entered greater than or equal this 07/12/2001 it does not work as see below the screenshot.

View attachment 69905

sorry error type. 07/12/2021.

 

[Dan_W]

That shouldn't be too complicated to solve, but before I think about it - are you certain you don't mind deleting the time component? You don't need to.

 

[vbanewbie68]

I do need the time component please.

 

[Dan_W]

Ok - will have a think about it when I get home from work, but in terms of your problem with the date - those results make perfect sense if you interpret 07/12/2021 as being 12 July 2021 and not 7 December 2021, no?

 

[vbanewbie68]

lt works when I enter 12/07/2021. So I think I should change format into this DD/MM/YYYY.

 

[Dan_W]

This might help you process the data into dates before filtering it:

Sub ProcessCells()
    
    Const DATEPATTERN = "dd/mm/yyyy"
    Const TARGETRANGE = "G2:G10"
    
    Dim CurrentCell     As Range
    Dim CurrentDate     As String
    Dim FormattedDate   As Date
    Dim TempDate        As Variant
        
    For Each CurrentCell In Range(TARGETRANGE)
        CurrentDate = VBA.Replace(CurrentCell.Value, "UTC", "")
        TempDate = Split(Application.Trim(CurrentDate), "T")
        FormattedDate = CDate(Format(TempDate(0), DATEPATTERN)) + CDate(TempDate(1))
        CurrentCell.Value = FormattedDate
    Next
    
End Sub