Converting a string value to a date in VBA

  • Hi

    So I'm working on a spreadsheet that uses exported data from another piece of software. One field that is exported is Time and date, however I am running into problems with my code when trying to use the date in a calculation (I need to compare the date exported with todays date and get the answer in days and see if this is greater than 60 days - I can do this bit with proper dates but not with strings) because it appears as a text string in excel rather than a date.

    The dates appear in the following format 25/08/2017 14:00, so this would be 25th of Aug 2017 at 14:00 (dd/mm/yyyy hh:mm).

    I have found a piece of code which sort of works when trying to convert this to an actual date.

    Sub Date_test()
    Dim c As Range 
    For Each c In ActiveSheet.UsedRange.Columns("A").Cells
    c.Value = DateValue(c.Value) 
    Next c
    End Sub

    But doesn't convert everything correctly, as sometimes the date could represent a US or UK date. E.g 08/09/2017, could either be 08 Sept 2017 (correct) or 09 Aug 2017 (incorrect- for me) Is there a way (through VBA), that I can convert the text string I have into a date excel will recognise and in the format dd/mm/yyyy (time isn't important), without converting it to a mm/dd/yyyy format accidently? Thanks

  • Nevermind I solved it (not sure if posts can be deleted)

    I separated the original string using left mid and right into 3 separate cells then used the =DATE formula to assemble the date as I needed.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!