excel formatting
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 this formatting it only recognises 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 |
=TIME(INT(B1/10000)-INT(A1/10000),INT(MOD(B1,10000)/100)-INT(MOD(A1,10000)/1
00),MOD(B1,100)-MOD(A1,100)) and form at as time -- 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 this formatting it only recognises 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 |
You cannot use formatting as an input mask to enter times or dates without the
proper separators. Use the colon or see Chip Pearson's site for entering times/dates using VBA event code. http://www.cpearson.com/excel/DateTimeEntry.htm OR Bob Phillips' site for an add-in for Quick Date Entry. http://www.xldynamic.com/source/xld.QDEDownload.html Gord Dibben Excel MVP On Tue, 19 Apr 2005 07:18:05 -0700, "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 this formatting it only recognises 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 |
All times are GMT +1. The time now is 08:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com