Convert time and date which are text to proper time and date

[uk747]

I have times in H1:Q1 which are formatted as hh:mm but are actually text
E.g. 04:20. 05:11

I also have dates in h3:Q3 which are formatted as dd-mm-yy are also text

I would like VBA to convert the times to proper times and dates to proper dates

Thanks

 

[EFANYoutube]

Hi uk747

Just use =DateValue() & =TimeValue()

 

[uk747]

Thanks
I know about the formulas, but how would I use it within VBA to convert the 2 ranges

 

[EFANYoutube]

TrueTime = TimeValue("05:30")

 

[EFANYoutube]

Thanks
I know about the formulas, but how would I use it within VBA to convert the 2 ranges

It depends what you want to do, do you want the value of the cell to be replaced by the true time/date?

 

[EFANYoutube]

Heres a function you can use

Function ConvDateTime()
    Dim cell As Range
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("H1:Q1").Cells
        cell.Value = TimeValue(cell.Value)
    Next cell
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("H3:Q3").Cells
        cell.Value = DateValue(cell.Value)
    Next cell
End Function

 

[uk747]

Thanks

If I have it in a function, do I just call it from a sub

 

[EFANYoutube]

Yes correct like

Sub calldt
ConvDateTime()
End sub

 

[uk747]

Ok thanks

 

[EFANYoutube]

No problems and thanks for your feedback