incorrect time entry

Question

I have a spreadsheet with nearly 500 time entries in it in a single column, and about 20 columns with time entries in total. The person who entered the data has been inconsistent in how it was entered - I have some as e.g. 14:30 and some as 14.30 (one with colon, one with period).

This makes it really hard to do any maths with the numbers as I can't seem to get them into a format that an Excel equation can understand, even when I try changing the format into the various time formats. They always remain as a time containing a period.

Any suggestions please? I can't manually go through all the cells without shooting myself afterwards.

Answer 1

Hi,

Have you tried using Find and Replace. Find for . and replace with :

Answer 2

If you want to change the decimals to time whilst leaving the correct times you will need to write a macro, something like the following.

To run this, go to the sheet in question:

• Copy the specific sheet, or save the workbook to a different name just in case
• press <Alt-F11> to open the VBA editor
• Right click on this sheet's VBA Project, then select Insert>Module
• Copy/Paste the below code into the Code (right-hand) window
• Press <F5> to run

This code loops through all used cells, it ignores any cells containing text, formulas, or actual times, so will only convert numbers.

It is assuming all times to be converted have been entered as you specified (eg "12.33") so no seconds entered.

If the cell cannot be converted to a valid time (eg it contains "12.99") then the cell value will be left as is and the cell will be highlighted in yellow.

Sub FixTimes() 

  Dim rCell As Range 

  Dim iHrs As Integer, iMins As Integer 

  Dim lChanged As Long 

  Dim dTime As Double 

  On Error Resume Next 

  For Each rCell In ActiveSheet.UsedRange 

    'Only process cell if it is a number and it does not contain a formula 

    If rCell.HasFormula Then 

      'Do nothing, this is a formula 

    ElseIf IsNumeric(rCell.Value) Then 

      'Time converted so set time and remove any colouring so only failures are yellow 

      rCell.Interior.ColorIndex = 0 

      rCell.NumberFormat = "hh:mm" 

      If rCell.Value > 1 Then 

        'Time values will be less than zero, or they will be a date 

        iHrs = Int(rCell.Value) 

        iMins = (rCell.Value - iHrs) * 100 

        dTime = TimeValue(iHrs & ":" & iMins) 

        If Err.Number <> 0 Then 

          'Time was invalid (eg 12.99) so leave cell value and highlight 

          rCell.Interior.Color = vbYellow 

          rCell.NumberFormat = "General" 

          Err.Clear 

        Else 

          'Time converted so set time and remove any colouring so only failures are yellow 

          rCell.Value = dTime 

          lChanged = lChanged + 1 

        End If 

      End If 

    End If 

  Next 

  MsgBox "Complete, corrected " & lChanged & " cells out of " & ActiveSheet.UsedRange.Count 

End Sub

Answer 3

Thanks for the reply.

Correct me if I’m wrong but this is for a spreadsheet before data entry?

Unfortunately this is data that usually is already entered so I don’t think that will apply here? I need to change the entries after they’ve been entered to the correct format eg 17:30 from 17.30.

Answer 4

use data validation:

Excel Data Validation Guide | Exceljet