vba to change time to 24 hour

[gilnic]

Hello,
Hoping someone may be able to help me please.From the site I go to I click on download everyday and it sends an excel file down to me.I then copy the required information from that as per sample sheet.Now what I would like to be able to do is convert that time to 24 hour time with a vba macro.
The times usually start about 10am in the morning and go through til about 9pm at night.
So while there is no am/pm on the sheet there would not be any say 10pm to confuse the issue.When I copy from the downloaded Excel sheet and paste I do not format the column that the time is pasted into as shown in sample.

[Logit]

.
How are you to determine if these times are AM or PM without some indicator already present ?

If you were to convert the times as is, VBA would not understand which is which.

[mikerickson]

If all the times are between 10am and 9pm, a formula like =TIMEVALUE(A1)+IF(FIND(":",A1)>2,0,0.5) should convert them into excel serial times. Formatting would add the AM or PM as needed.

[rcm]

this would place the formated 24hr time in column f

Please Login or Register  to view this content.

[gilnic]

Hello,
Thank you both for prompt response to my query.
@Logit The times run in order so if there was 10:30 it would be at start then 11:30 followed by 2:30,7:30.
@mikerickson Thank you for such a sweet solution.
I have enclosed sheet showing your answer as suggested which I have been able to convert to the time format I require.

[gilnic]

Hello RCM,
I replied before I was notified re your response.Thank you very much.I now have two ideal solutions.
Appreciate you taking the time to help.

[rcm]

Any time. You asked for a VBA solution.

[AliGW]

This thread is closed as a duplicate. In future, if you are happy with either a formula or VBA solution, make this clear in your thread title. You are allowed one thread per issue here only - continue in the other thread, please: https://www.excelforum.com/excel-new...o-24-hour.html