Take two hours from 00:00 and show 10:00 not ######

[swanseaexcel]

Hi all,

I have a formula that takes a variety of production times for our manufacturing business (i.e. 06:00 equals 6 hours production time) and subtracts them from a finish time to derive start time.

For example, if finish time is 02:00 and production time is 06:00 then finish time less production time should equal 20:00.

However when I attempt this formula I get a ##### answer. Any way around this?

Thanks.

[arthurbr]

Suppose finish time in A1 and prod time in A2, in another cell enter

Please Login or Register  to view this content.

and format as needed

[MarvinP]

http://support.microsoft.com/kb/182247 ?

[arthurbr]

http://support.microsoft.com/kb/182247 ?

Perfect MS answer ! Why make things easy when you can do them the hard way

[DonkeyOte]

Alternative to the addition of 1 would be to use MOD:

Please Login or Register  to view this content.

@Arthurbr - agreed - switching to 1904 Date System is not really ideal, nor is storing a Number as Text.

[ChemistB]

=IF(A1>A2, A2+1-A1, A2-A1)
Although MOD(A1-A2,1) is definitely cooler.

[arthurbr]

Chemist ,

I think my solution is the same as yours but a little shorter

[ChemistB]

Yes it is, pardon these tired eyes. Yours is also cooler.

[arthurbr]

Yes it is, pardon these tired eyes. Yours is also cooler.

Is it that early where you are?

[ChemistB]

It's 10:40 AM. I'd prefer to blame tired eyes than old eyes.

[swanseaexcel]

DonkeyOte my saviour yet again!

Thank you all for the replies, I used the MOD option as everyone said that was coolest!