You did remember to enter as array formula (as I wrote in #10)?

Attached Files

TimeFirst.xlsx (14.9 KB, 13 views)

yes, i remembered to hit shift+control+enter. when i test the file you attached, this is how the data looks after running the formula:

tt:01 john bob harry doe don
tt:01 jane jill sam
tt:01 lara tim jim

what could be wrong with it?

I use Danish settings.

"tt:mm" because hours in Danish is "timer". You should change "tt:mm" in the formula to "hh:mm"

I would have expected that Excel automatically would change "tt" to "hh", when opened on a system with US settings, but apparently not.

thanks xor, it's working fine now. i had tried the same thing, but it wasn't working quite right. i think i was overlooking something when changing A1's to C1's.

if i wanted to move the formula down on the sheet instead of across, what would i change then to make it work?

Select C13, press Ctrl+X (to Cut), select the upper left corner where you want to paste it to and press Ctrl+V. You can also, if you want, just Cut and Paste the formulas, that is select D13; press Ctrl+X (to Cut), select the upper left corner where you want to paste it to and press Ctrl+V.

Please note that Ctrl+X means a two key operation, Ctrl and X. The plus sign should not be entered.

Instead of this strange icon it should be c1:d3, respectively d1:d3.