Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Please help! I have used the 00:\00:\00 format to input times without having
to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you |
#2
![]() |
|||
|
|||
![]()
See previous post .misc
-- HTH RP (remove nothere from the email address if mailing direct) "Lexicon" wrote in message ... Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you |
#3
![]() |
|||
|
|||
![]()
I have a question for you! How do you format a cell with :00:\00:\00? I tried
creating this custom number format myself, but excel wouldn't allow it. "Lexicon" wrote: Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you |
#4
![]() |
|||
|
|||
![]()
It should be 00\:00\:00
-- HTH RP (remove nothere from the email address if mailing direct) "Brett" wrote in message ... I have a question for you! How do you format a cell with :00:\00:\00? I tried creating this custom number format myself, but excel wouldn't allow it. "Lexicon" wrote: Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you |
#5
![]() |
|||
|
|||
![]()
Brett - You just typed the numbers in wrong on your posting - that's probably
why it didn't work for you! In formatting, in custom, type in 00:\00:\00 and this should enable you to input for example 123245 and it will convert automatically to 12:32:45 Niki "Brett" wrote: I have a question for you! How do you format a cell with :00:\00:\00? I tried creating this custom number format myself, but excel wouldn't allow it. "Lexicon" wrote: Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you |
#6
![]() |
|||
|
|||
![]()
Thanks! I had no idea it was this simple. I've been looking for this solution
for a long time, but the closest I got was some really complicated VBA code I found on some Excel website a while ago. I never did bother with the VBA code, but this is great. Thanks again! "Bob Phillips" wrote: It should be 00\:00\:00 -- HTH RP (remove nothere from the email address if mailing direct) "Brett" wrote in message ... I have a question for you! How do you format a cell with :00:\00:\00? I tried creating this custom number format myself, but excel wouldn't allow it. "Lexicon" wrote: Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you |
#7
![]() |
|||
|
|||
![]()
No actually you typed it wrong, Brett just copied your error, I had the same
problem <g Bob "Lexicon" wrote in message ... Brett - You just typed the numbers in wrong on your posting - that's probably why it didn't work for you! In formatting, in custom, type in 00:\00:\00 and this should enable you to input for example 123245 and it will convert automatically to 12:32:45 Niki "Brett" wrote: I have a question for you! How do you format a cell with :00:\00:\00? I tried creating this custom number format myself, but excel wouldn't allow it. "Lexicon" wrote: Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you |
#8
![]() |
|||
|
|||
![]()
Yeah, but the math on times now becomes much more complex as against
-- HTH RP (remove nothere from the email address if mailing direct) "Brett" wrote in message ... Thanks! I had no idea it was this simple. I've been looking for this solution for a long time, but the closest I got was some really complicated VBA code I found on some Excel website a while ago. I never did bother with the VBA code, but this is great. Thanks again! "Bob Phillips" wrote: It should be 00\:00\:00 -- HTH RP (remove nothere from the email address if mailing direct) "Brett" wrote in message ... I have a question for you! How do you format a cell with :00:\00:\00? I tried creating this custom number format myself, but excel wouldn't allow it. "Lexicon" wrote: Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you |
#9
![]() |
|||
|
|||
![]()
How about:
=TIME(($B1-MOD($B1,10000))/10000,(MOD($B1,10000)-MOD($B1,100))/100,MOD($B1,100))-TIME(($A1-MOD($A1,10000))/10000,(MOD($A1,10000)-MOD($A1,100))/100,MOD($A1,100)) OR =TIME(MID(TEXT($B1,"000000"),1,2),MID(TEXT($B1,"00 0000"),3,2),MID(TEXT($B1,"000000"),5,2))-TIME(MID(TEXT($A1,"000000"),1,2),MID(TEXT($A1,"000 000"),3,2),MID(TEXT($A1,"000000"),5,2)) "Lexicon" wrote: Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Time Values | Excel Discussion (Misc queries) | |||
If Statement - in time not decimal minutes | Excel Worksheet Functions | |||
How do you find the difference between two time values when one i. | Excel Discussion (Misc queries) | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
How to convert decimal values (dd.dddddd) to degrees/minutes/seco. | Excel Worksheet Functions |