Auto Inserting AM or PM Reference Based On System Time Clock

[pctdoug]

I have an excel time tally sheet where users enter start and start times based on the "1:30 PM" non-24hr time format. With this approach, users must enter "_space_PM" for afternoon and evening entries, in order to prevent Excel from defaulting to creating an AM entry.

The problem is, users sometimes forget to enter the "_space_PM" part, which then messes up the downstream formula calculations.

Is there a way for us to continue entering times using the non-24hour format, but without having to manually type "_space_PM" for the afternoon and evening entries? I'm wondering if there is a way to have Excel determine whether it is AM or PM based on the system clock. We are working in a PC windows environment.

Thanks,
Doug

 

[gordsky]

@gordsky or anyone - after thinking this through a bit more, I can see that it is not that the solution stopped working... it is just that in order for the solution to work, any afternoon numbers must be added using the 24hr formatting system which then auto converts to the AM/PM system. So, if I want "1:30 PM" to show in the cell, I have to enter "13:30"

What I was actually hoping for, is to simply enter "1:30" in the cell and have it auto convert to "1:30 PM"

Do you know if there is a formatting option (or a formula) that will do that?

Not sure why it would suddenly stop working but you could try a coded approach in the sheet. The code assumes your time is being entered into ColA. If not just change the column reference to where ever your time is being entered. Add this code to the "SheetCode" (right click on tab - view code) not to thisworkbook or a module

Private Sub worksheet_change(ByVal target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = false
Dim mystr As String

If target.Cells.count = 1 Then
  
    If Not Application.Intersect(target, Range("A:A")) Is Nothing And target.Value <> "" Then
       target.NumberFormat = "general"
       mystr = Format$(target, "hh:mm")
                  
       If left(mystr, 2) < 12 Then
            If Format(Now(), "AM/PM") = "PM" Then
               target = mystr & " " & "PM"
            Else
               target = mystr & " " & "AM"
            End If
          
        Else
          target = Format$(target, "h:mm AM/PM")
       End If
         
    End If

End If
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

If the user includes the AM/PM switch it will show what they entered, if they dont it will take the AM/PM switch from the system clock (as you said time is entered in real time)

 

[pctdoug]

I've changed Range ("A:A") to Range ("F:F") since my input cells are in ColF
... added the code to my relvant Sheet1
... and saved as an .xlsm file to allow the macro to run.

Now I wait until after 1 PM to test
... will let you know.

Thank you very much for your ongoing assistance with this.

 

[pctdoug]

@gordsky - the macro works... yay! Thanks again.