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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
497
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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.
 
Last edited:

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
497
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Aug 1, 2018
Messages
497
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top