Formatting time in Excel

[jpe2007]

Hi all, I hope there is a simple solution.

I run a call center and I would like to input a time value into cell A1.

For example 01:45:03 - this would represent 1 hour, 45 minutes and 3 seconds.

Currently I have three cells to accomplish the representation one cell for hours, one for minutes and one for seconds.

My sheets are getting rather long and there must be a better way.

So ultimately how can i enter 014503 and have it appear in A1 as formatted above?

Please someone smarter than I will have the solution.

Waiting eagerly for your response.

 

[SkipVought]

Hi,

First understand what happens in Excel when you enter data.

faq68-5827

This can only happen using VBA code. Please post in Forum707 for help with a VBA solution.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

first, you should definitely read Skip's FAQ to understand how Excel handles dates/times.

Your question is basically, "How do I tell Excel that I am entering times in a single cell?"

The answer is: [!]use colons!!![/!]

Enter [COLOR=blue white]01:45:03[/color] into a cell. If you use the colons, Excel is smart enough to realize that you are entering a time and it will convert and store it properly.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Hi jpe2007:

If using an extra column will work for you the following formula based solution might be of interest to you ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

[completely offtopic]

yogia:

Is there any way you could drop off the string that follows all of your pictures ([blue][ignore]thread68-1423997: Formatting time in Excel.gif" border="0">[/ignore][/blue] in the above picture)?

[/completely offtopic]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

jpe2007: Where are you getting these times?

I ask because I have worked in or done work for a call center for over 10 years and I strongly discourage folks from using manual entry in reports.

Whatever application you are using to give you this time - whether it is from a call server or data tracking software - can likely export data electronically so you never have to type anything in.

Just something to think about.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Is there any way you could drop off the string that follows all of your pictures (thread68-1423997: Formatting time in Excel.gif" border="0"> in the above picture)?

Hi John:

What I upload is an image of the part of my worksheet ... and the string that attaches itself to the end of the image is sort of automatic -- I know it doesn't belong there. So far I have not looked into stopping it from happening, but let me check if I can suppress it or delete it. I will let you know how it goes.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi John:

A little study of the situation regarding the string that attaches itself to the bottom right end of the image reveals that the string is being appended by tek-tips.com reflecting the ID of the thread and the SUBJECT of the thread followed by the border type of the image.

So that is where it is now ... I don't know if I want to pursue it further. I was able to use a few blank lines of my spreadsheet in the image that I created but tek-tips.com appended the string at the bottom right hand corner of the image as depicted below ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[PeterMoran]

Hi,

In addition to Yogi's solution, Chip Pearson has an excellent one at:

http://www.cpearson.com/Excel/DateTimeEntry.htm

Which does not require an extra cell. It's one drawback is that it will not allow you to enter a formatted date with colons - which I have found is easy to do inadvertantly.

If you want to use this solution then I can give you my changes to permit the entry of valid time into the cell.

Good Luck!

Peter Moran

 

[AnotherHiggins]

[still off topic]

Yogi, it's no big deal. It's just a bit distracting to my eye, and I'm a little anal about stuff like that.

I haven't used FreeDrive before, but, FWIW, it looks like you are using an extended link path which includes extra formatting information that might be appropriate for other message boards.

Examples:​

[ignore]

[/ignore]

[tab]produces:

^^
Note the trailing data

Now drop that extra data from your link:

[ignore]

[/ignore]

[tab]displays as:

Or you can go further and truncate it down to the image ID...

[tab]Just typing

[ignore]

[/ignore]

[tab]is all it takes to give us the desired:

[/still off topic]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Yogi, it's no big deal. It's just a bit distracting to my eye, and I'm a little anal about stuff like that.

I haven't used FreeDrive before, but, FWIW, it looks like you are using an extended link path which includes extra formatting information that might be appropriate for other message boards.

Hi John:

Thanks for your valuable insight. I have to digest it, try it out ... so images in my future posts will be clean. Thanks again.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com