Custom formatting time question

**Mr_Phil** · 08-18-2018, 10:18 PM

Good evening. I have a few spreadsheets that use start and end time to determine elapsed time. I use the 24 hour military format because it is instinctual for me and I don't have to explicitly type am or pm. I would also like to not have to leave the number pad to type a colon. I did a custom format that I thought would address that. It did after a fashion. If i type 13:00 the colon will disappear when I hit either enter or tab. But if I simply type 1300 it changes to 0000 when I move to the next cell. The closest I have come is that I made an autocorrect entry so I type "--" and it becomes a colon, which disappears when I move to the next cell. Can anyone suggest a different approach?

Capture.PNG

**FDibbins** · 08-19-2018, 12:22 AM

Originally Posted by Mr_Phil

...I would also like to not have to leave the number pad to type a colon....

doing that will enter a whole number into the cell, not a tile.

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sun 19 Aug 2018) is actually 43331

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

Seeing as you have now entered 1300 (one thousand three hundred) into the cell, no TIME formatting will change that

**Mr_Phil** · 08-19-2018, 06:41 PM

I did more research and came up with the same conclusion. It is what it is. Oh well. The autocorrect for a double minus sign will allow me to keep up the pace of the data entry without too big a problem. Thanks for reading and providing some insight.

**protonLeah** · 08-19-2018, 07:02 PM

If you have a column (L) for times, you can custom format it with 00\:00 to show 13:00 when you type in 1 3 0 0 (or just leave it as is without the custom format). But, to calculate with the entry as a time, you will use a (hidden) helper column (M) with the formula: =TIME(INT(L1/100),MOD(L1,100),0)

**FDibbins** · 08-19-2018, 11:02 PM

Thanks for the feedback

Custom formatting time question

LinkBack

Thread Tools

Rate This Thread

Display

Custom formatting time question

Re: Custom formatting time question

Re: Custom formatting time question

Re: Custom formatting time question

Re: Custom formatting time question

Thread Information

Users Browsing this Thread

Similar Threads

Custom Button > E-mail. Hyperlink and text formatting question.

Custom Formatting of Time

custom formatting question (easy?)

Custom formatting question

A question on Custom Formatting

[SOLVED] Custom Formatting numbers question

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions