Can I auto convert an entry of digits to time format?

[RockandGrohl]

Here's a weird one.

Due to the sheer laziness of some team members, they want to enter "1730" into a cell and have it convert to "17:30"

Likewise, "530" would convert to "05:30" (5:30AM)

I'm literally rolling my eyes because they can't be bothered to just physically write "05:30" (and yes, I've asked them)

Is there a way to automate this? I can't see anything so far. Thank you.

 

[Special-K99]

Custom format

00":"00

BUT

Beware that entering the time as 0530 or 530 the value in the cell is still five hundred and thirty, not 05:30 (time) even though it displays as such.
You'll have to convert it if you want to do calculations on that entry.

 

[RockandGrohl]

Custom format

00":"00

BUT

Beware that entering the time as 0530 or 530 the value in the cell is still five hundred and thirty, not 05:30 (time) even though it displays as such.
You'll have to convert it if you want to do calculations on that entry.

That's great, thanks. It's no problem - we're not calculating time, just printing off to supply to people. Awesome!

 

[RockandGrohl]

Custom format

00":"00

BUT

Beware that entering the time as 0530 or 530 the value in the cell is still five hundred and thirty, not 05:30 (time) even though it displays as such.
You'll have to convert it if you want to do calculations on that entry.

Ah, we already have time stored in two columns as 00:00:00 format, displayed as 00:00, can these be converted back to string so that going forward when a user enters a string it ends up as "hh:mm"? Thanks.

 

[Special-K99]

Not sure what you're asking for

Text values to Excel time
=TIMEVALUE(LEFT(TEXT(B2,"0000"),2)&":"&RIGHT(TEXT(B2,"0000"),2))

Numbers entered as four digits converted to what looks like time
=LEFT(TEXT(B2,"0000"),2)&":"&RIGHT(TEXT(B2,"0000"),2)