Time issue when collected times not in 24hr format

[SQUIDD]

Hi All

So this one has been getting to me. Im trying to simply convert the afternoon times to 24hr.
I collect data from a website. Some of this data is times, but all in time order. like below. Before 12 is am and after 12 is pm.
When i run the code to obtain these times, it will only ever give me future times. So, as below would be if i run the code to get the times in the morning.

8:12
9:57
10:27
11:34
12:22
01:02 - i would like to get 13:02
02:34 - i would like to get 14:34

if i run the code to obtain the times in the afternoon, it would look like this

12:22 i would like to get 12:22
01:02 i would like to get 13:02
02:34 i would like to get 14:34
03:33 i would like to get 15:33
04:12 i would like to get 16:12
10:33 i would like to get 22:33 - just to cause issue

I hope the obove makes sense.

I will continue to try to figure it out, i was trying to be clever and convet as i obtained the time during my for loop when collecting elements from website, but make change this and simlly run a routine at the end to covert.

thanks for looking

Dave

 

[SQUIDD]

ok, so i have this so far as a test code

i think it will work fine, but i cannot get the time to ad 12 hours

my list of times in column AC
my converted output in AD
have a temp AM/PM setter to trial both without obtaining actual AM/PM for now, will change after working

Sub convert_time()
afternoon = ""
ampm = "am"
If ampm = "am" Then

    For a = 1 To 61
        If afternnon <> 1 Then
            If Format(Range("ac" & a), "hh") = 12 Then afternoon = 1
        End If
        If afternoon = 1 And Format(Range("ac" & a), "hh") <> 12 Then
            Range("ad" & a) = Range("ac" & a) + TimeValue("12:00:00") 'add 12 hours not working
        Else
            Range("ad" & a) = Format(Range("ac" & a), "hh:mm")
        End If
    Next a
Else
    For a = 1 To 61
        If Format(Range("ac" & a), "hh") <> 12 Then
            Range("ad" & a) = Range("ac" & a) + TimeValue("12:00:00") 'add 12 hours not working
        Else
            Range("ad" & a) = Format(Range("ac" & a), "hh:mm")
        End If
    Next a
  
End If

End Sub

 

[SQUIDD]

ok, i think i have it

Sub convert_time_a()
afternoon = ""
ampm = "pm"
    For a = 1 To 61
        If ampm = "am" Then
            If afternnon <> 1 Then
                If Format(Range("ac" & a), "hh") = 12 Then afternoon = 1
            End If
            If afternoon = 1 And Format(Range("ac" & a), "hh") <> 12 Then
                Range("ad" & a) = Format(DateAdd("h", 12, CDate(Range("ac" & a))), "hh:mm")
            Else
                Range("ad" & a) = Format(Range("ac" & a), "hh:mm")
            End If
        Else
            If Format(Range("ac" & a), "hh") <> 12 Then
                Range("ad" & a) = Format(DateAdd("h", 12, CDate(Range("ac" & a))), "hh:mm")
            Else
                Range("ad" & a) = Format(Range("ac" & a), "hh:mm")
            End If
        End If
    Next a
End Sub

 

[SQUIDD]

sometimes, when you are forced to explain to others what you are trying to achieve, you understand yourself and have a moment of clarity lol