How do I batch convert dd/mm/yyyy HHMM to dd/mm/yyyy HH:MM-CodePudding

I have several thousand entries and i'm trying to get the differences between two times. If I manually convert dd/mm/yyyy HHMM to dd/mm/yyyy HH:MM then I can use a simple =K-J formula in the next column to do this. But how do I get that darn colon into the many entries without having to manually go through and do it? Thanks

CodePudding user response：

Have you tried the TEXT function?

=TEXT([Your cell reference],"dd/mm/yyyy HH:MM")

Alternatively, if the original value is being stored as string you could try:

=MID([Your cell reference],1,LEN([Your cell reference])-2) & ":" & RIGHT([Your cell reference],2)

CodePudding user response：

Since your title suggests a uniform format of "dd/mm/yyyy HHMM", which excludes variations such as "dd/mm/yyyy HMM", etc., and so guarantees a string length of 15 characters, then, assuming entries in A1 and B1:

=0 REPLACE(A1,14,,":")

and

=0 REPLACE(B1,14,,":")

Or, without helper columns, and assuming that B1>=A1:

=REPLACE(B1,14,,":")-REPLACE(A1,14,,":")