On 2002-03-23 09:59, Brian from Maui wrote:
Aloha,
I posted this question yesterday, and I'll try again. Our company deals in military time also. We (not personally) keeps a log, and one of the major complaints is inputting the colon. Using the custom format 00:00, they can't add/subtract times. Is it possible for a macro to add the colons and still be able to add/subtract. I'm not looking for someone to write the code, just if it's possible.
Brian
Hi Brian:
We discussed this in yesterday's post that started with CJ, and we looked at a number of ways of doing this including using the custom format as you stated here. The custom format 00:00 works with numbers only, so there is a problem applying it with times with trailing 0's ... in which case there will be only 1 digit in the hours. So that will have to be taken into account.
However, as discussed at the tailend of yesterday's thread, you can convert your time entry '0830 into time by
'0830 =time(left(a2,2),right(a2,2),0))
and then you can use the new entry to add / subtract, or whatever.
If I had '0830 in cell A2, and '0600 in B2
'0830 - '0600 in cell C2 ... result formula
=time(left(a2,2),right(a2,2),0)-time(left(b2,2),right(b2,2),0)
=2:30:00
then if you want to format this as hours in decimal notation
=(a2-b2)/24 formatted as NUMBER|General gives
=2.5
HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there!
_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard coded signature
This message was edited by Yogi Anand on 2003-01-19 15:49