I'm unsure if this is the correct place for this. I do think that what I'm trying to accomplish will require a macro. Essentially I would like to type "2300" in a cell and have it display time as 23:00. When I do this, it thinks its a date and displays "4/18/1906 12:00:00 AM". I would like to avoid the need to put a colon in between the hours and minutes. If anyone can give me any insight as to how to approach this it would be greatly appreciated.
Here you will find formula as well as a macro for your request: https://excel.tips.net/T002775_Conve..._to_Times.html
Click the * to say thanks.
Make 1 column to enter time no colon,and another to parse it WITH.
=Concatenation(left(a1,2),":",right(a1,2))
Last edited by ranman256; 07-19-2018 at 10:55 AM.
I'm not sure what you mean. Could you show me a simple example on a spreadsheet so I can try and mimic the effect?Originally Posted by ranman256
Put this event in the sheet module:
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
There is a formatting / cell mask trick that can present 2300 as 23:00
the cell format should be 00\:00
see attachement
Using Roel's formatting cell mask makes a better event!
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks