- Joined
- Nov 4, 2009
- Messages
- 1
- Reaction score
- 0
Wow, I'm frustrated. I've searched and found many people looking for the solution to my problem but have yet to see a definitive answer that I can get to work for me.
I need to be able to enter a four digit number into cell A1 representing a military time (**NO COLON**, i.e. 0630) and another military time in cell B1 with the same format. I then need to perform computation on those two times in order to determine the duration between the two.
I would like a four digit number entered into A1 or B1 to be recognized as "hhmm" but so far have been unable to get excel to cooperate. My solution after searching was to use:
=TIME(LEFT(TEXT(A1,"0000"),2),RIGHT(TEXT(A11,"0000"),2),0)
This successfully converts convert the four digit number to time in "hhmm" and then I hid that operation on a different sheet so the needless conversion doesn't clutter up the portion of the spreadsheet that is seen. Then a simple =Z1-Y1 operation successfully displays duration in "hhmm" format (where Z1 and Y1 are the cells I hid the conversion to time format in). The problem is that this setup cannot accommodate a situation where the start and end times roll over 0000 (midnight). My solution was:
=IF(Z1>Y1,Z1-Y1,((Z1+(TIME(24,0,0)))-Y1))
That way in the situation that start time is 2355 and end time is 0030 the formula would effectively run the computation 2430-2355...which would still return a proper duration.
Excel doesn't like this and returns "#####" in the situation that the IF/THEN isn't met and it proceeds on to the ELSE operation. I can't figure out why and it's driving me insane. This seems a simple process to me but I can't see where I've gone wrong.
Ultimately I need to display duration in hours and tenths of hours (e.g. 3.2) where:
Minutes-------- Tenths
57-03------------ .0
04-09------------ .1
10-15------------ .2
etc.-------------- etc.
I'm an excel novice and I figured programming the IF/THEN sequence necessary to do that from the calculated duration in "hhmm" was going to be the hard part...but I can't even get past the first step.
Can anybody provide some insight on what I'm doing wrong? A better more efficient way of going about this? Or if I'm on the right track just a troubleshoot of my formula above and why it isn't accepted by Excel?
I greatly appreciate any help that can be offered.
Thanks.
I need to be able to enter a four digit number into cell A1 representing a military time (**NO COLON**, i.e. 0630) and another military time in cell B1 with the same format. I then need to perform computation on those two times in order to determine the duration between the two.
I would like a four digit number entered into A1 or B1 to be recognized as "hhmm" but so far have been unable to get excel to cooperate. My solution after searching was to use:
=TIME(LEFT(TEXT(A1,"0000"),2),RIGHT(TEXT(A11,"0000"),2),0)
This successfully converts convert the four digit number to time in "hhmm" and then I hid that operation on a different sheet so the needless conversion doesn't clutter up the portion of the spreadsheet that is seen. Then a simple =Z1-Y1 operation successfully displays duration in "hhmm" format (where Z1 and Y1 are the cells I hid the conversion to time format in). The problem is that this setup cannot accommodate a situation where the start and end times roll over 0000 (midnight). My solution was:
=IF(Z1>Y1,Z1-Y1,((Z1+(TIME(24,0,0)))-Y1))
That way in the situation that start time is 2355 and end time is 0030 the formula would effectively run the computation 2430-2355...which would still return a proper duration.
Excel doesn't like this and returns "#####" in the situation that the IF/THEN isn't met and it proceeds on to the ELSE operation. I can't figure out why and it's driving me insane. This seems a simple process to me but I can't see where I've gone wrong.
Ultimately I need to display duration in hours and tenths of hours (e.g. 3.2) where:
Minutes-------- Tenths
57-03------------ .0
04-09------------ .1
10-15------------ .2
etc.-------------- etc.
I'm an excel novice and I figured programming the IF/THEN sequence necessary to do that from the calculated duration in "hhmm" was going to be the hard part...but I can't even get past the first step.
Can anybody provide some insight on what I'm doing wrong? A better more efficient way of going about this? Or if I'm on the right track just a troubleshoot of my formula above and why it isn't accepted by Excel?
I greatly appreciate any help that can be offered.
Thanks.