Formula Not Calucating Right

[jfcby]

Hi,

I have a work sheet with the following cells formated as Custom h:mm
AM/PM with the data:

D13 E13 F13 G13
8:00 12:00 12:30 4:30

and in cell H13 has the following formula:
=(E13<D13)+E13-D13+(G13<F13)+G13-F13

The problem is cell H13 total is 20:00 hours but should be 8:00 hours.

I have two questions:
1. How can the above formula be changed in cell H13 to total 8 hours.
2. What needs to be done so that cell H13 data looks like this 8 or
8.5 instead of 8:00 or 8:30?

Thank you for your help,
jfcby

 

[Sandy Mann]

1) Your 4:30 is 4:30 AM not 4:30 PM

2) Multiply the answer by 24 and format as General

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[RagDyeR]

Your data in G13 is registering as 4:30 *AM*.

As you described your custom format, the AM *should* be visible in the cell!

If you don't want to enter your times as
16:30
Then enter them as
4:30<space>p

which will make XL read the correct, intended input.

For your second question, revise your formula to:
=((E13<D13)+E13-D13+(G13<F13)+G13-F13)*24
And format H13 to General or Number.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Hi,

I have a work sheet with the following cells formated as Custom h:mm
AM/PM with the data:

D13 E13 F13 G13
8:00 12:00 12:30 4:30

and in cell H13 has the following formula:
=(E13<D13)+E13-D13+(G13<F13)+G13-F13

The problem is cell H13 total is 20:00 hours but should be 8:00 hours.

I have two questions:
1. How can the above formula be changed in cell H13 to total 8 hours.
2. What needs to be done so that cell H13 data looks like this 8 or
8.5 instead of 8:00 or 8:30?

Thank you for your help,
jfcby