Text Time to Excel Format, Then Sort Time Past Midnight

I need some advice please:

I am supplied with a schedule (generated from a server) which lists times in the following format as an example:

Given Text Time
2330
5
40
115
150
220
2310
2340

Not very neat & tidy! I have a formula that works for every time, except for the hour past midnight. =--(LEFT(C2,LEN(C2)-2)&":"&RIGHT(C2,2)) (thanks to this forum)

Times such as '5' and '40' result in a #Value! error.

I also need to sort the times past midnight as in.... 23:30 would come before 00:05

Any help would be greatly appreciated.

Hi,

I have updated the new formula in column E in the attached file. Please see if this suits your requirement.


Regards,
Chandra

Please click on ‘* Add Reputation’ if this was helpful

Using the TEXT function to format the time to 4 digits, inserting a ":" and passing it to the TimeValue function seems to work well:

Please Login or Register  to view this content.

PS Having a hard time posting my reply, sorry if it posts twice.

Thanks for the reply guys.

Chandra - if you copy down the formula it contains errors for some reason. Thanks for taking a look at it.
SnarlingSheep - Your formula works well. Many Thanks

Now the problem on how to sort the times past midnight?