Format a time without entering the :, but keep it as a time

[ashley91481]

I have used a custom format so that I can enter a time as 115808 and have it show up as 11:58:08. Unfortunately, now i need to do calculations and the calculations are not correct b/c it is not recognizing this as a time. All of the "solutions" I've found for this don't include the seconds or require knowledge of visual basic. Is there a simple wait to convert this number 115808 into a time so it can be used in calculations?

 

[BenMiller]

The value for 11:58:08 is actually 0.498703703703704, which is the fraction of the day. To have Excel convert your human-readable time into this value (remember, you're changing the value; not just the format), I believe the only way to do so is to enter it with the colons - 11:58:08.

 

[pgc01]

Hi

Welcome to the board

Try:

=TEXT(A1,"00\:00\:00")+0

 

[BenMiller]

Hi

Welcome to the board

Try:

=TEXT(A1,"00\:00\:00")+0

Or a longer function, =TIME(LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2),RIGHT(A1,2))

but I don't know is ashley91481 is looking for a formula in another cell ...

 

[pgc01]

but I don't know is ashley91481 is looking for a formula in another cell ...

Hi Ben

I did not mean it had to be a formula in another cell.

Ashley says the conversion is needed for calculations, and so the formula can be used directly in calculations.

For ex., to add 10 time values:

=SUMPRODUCT(TEXT(A1:A10,"00\:00\:00")+0)

 

[BenMiller]

I stand corrected!

(I get tired out from standing corrected all the time )

 

[mikerickson]

To convert 2345 to the time 0:23:45, one could use
=TIMEVALUE(REPLACE(REPLACE(RIGHT("000000"&A1,6), 5, 0, ":"), 3, 0, ":"))

 

[ashley91481]

Thanks the first one seems to have worked. Additionally, we've tried copying and pasting from excel to text edit (mac) and then pasting back into Excel and reformatting it as a time. Not an elegant solution, but there are a few places where we have 6 columns of times and adding another column next to each so we can convert the time back is a bit of a pain.

It seems like a lot of people have had this issue though from my search on the internet - which makes it seem like there should be a simpler way to enter time quickly AND keep it formatted as a time for calculations.

Thanks!

 

[Jonmo1]

Simple...no not really.

How can Excel be expected to distinguish if you are entering a regular number or a time?

Take 115808 for example
How can Excel know if that is supposed to be
one hunderd fifteen thousand eight hundred eight...
Or
11 hours 58 minutes 8 seconds?

That's what the : does.
Tells excel that it's a time.


You might go into Control Panel - Regional Settings
Customize
Time
Change the seperator to a period.

That way you can enter time like

11.58.08

It's certainly much easier to press . than :

 

[ashley91481]

jonmo1, I'm not expecting Excel to automatically know what I want to do, just seems like there should be a setting or something for easily entering a time and formatting it with : and then still keeping it in a way that I can use it in calculations. Seems like I'm doing a lot of "work arounds". If I was the only person having the issue, I could understand that, but seeing how many people are going through the same issue, it seems like there should be something built into the program to help with it. Thankfully you all are here and have provided me some help, otherwise I would be lost