Subtracting 50 minutes from a formula

[JoeM]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Please help !

I have a formula ( =TEXT(J6-F6,"h:mm&quot that subtracts two times, yielding a result, formatted as 9:30 . This obviously is nine hours and thirty minutes.

I am trying to now (in another cell) subtract 50 minutes from the results cell with 9:30 in it, and show this new result formatted at h:mm

Many Thanks

 

[CyberTaz]

Assume your calculated result is in cell J10 try this, including the quotes:

=J10-"00:00:50"

You can throw a set of parens around the time for neatness if you wish ;

=J10-("00:00:50")

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[JoeM]

Many Thanks Bob.

When I tried it, it yielded the following result:

.39525462962296

I guess I now need to format this result to show it as hours and minutes? Would I need to add a format into this cell?

Lastly, I want to take this result (which should be 9.5 hours less 50 minutes) and then use it as a divisor in a related cell formula

Again, many thanks !

 

[John McGhie]

Yes, just format the cell as a time.

A "Time" in Excel is a decimal fraction of a Day (i.e. 24 hours).

..39525462962296 is 9 hours 29 minutes. I think you subtracted 50 "seconds".
You wanted to subtract 50 "minutes" so =J10-"00:50:00"

Cheers

Many Thanks Bob.

When I tried it, it yielded the following result:

.39525462962296

I guess I now need to format this result to show it as hours and minutes?
Would I need to add a format into this cell?

Lastly, I want to take this result (which should be 9.5 hours less 50 minutes)
and then use it as a divisor in a related cell formula

Again, many thanks !

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[CyberTaz]

Oops!!! That's my bad ‹ I put the em-FA-sis on the wrong sy-LA-ble in the
example in my reply $>}

Regards |:>)
Bob Jones
[MVP] Office:Mac