This should be easy, but I'm missing something. We have a deadline of
9:05. The actual time was 9:12. so I have 9:05 in cell B6 and I have
9:12 in cell C6. In D6 I want to show that we were 7 minutes
late...preferably that would show -7.

If I use the formula in D6 =MINUTE(c6-b6) The result is 7. Ideally I
would like fo that to be -7.

If we were actually ahead of deadline, say 9:00 a.m. the formula
returns #NUM! and I would like to show a positive number.

What am I doing wrong? Thanks for any help.


--
jonhunt
------------------------------------------------------------------------
jonhunt's Profile: http://www.excelforum.com/member.php...o&userid=35370
View this thread: http://www.excelforum.com/showthread...hreadid=557829

jonhunt wrote:

This should be easy, but I'm missing something. We have a deadline of
9:05. The actual time was 9:12. so I have 9:05 in cell B6 and I have
9:12 in cell C6. In D6 I want to show that we were 7 minutes
late...preferably that would show -7.

If I use the formula in D6 =MINUTE(c6-b6) The result is 7. Ideally I
would like fo that to be -7.

If we were actually ahead of deadline, say 9:00 a.m. the formula
returns #NUM! and I would like to show a positive number.

Hi jonhunt

To eliminate the #NUM! error:

Tools Options Calculation check 1904 date system.

Note that this will change any dates that already exist in your sheet
and you will need to amend them.

It's your sheet but if something is late, isn't it at "zero hour
+minutes" rather than zero hour -minutes"?

Regards

Steve

Use this formula in D6

=(B6-C6)*1440


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=557829

"jonhunt" wrote in
message ...

This should be easy, but I'm missing something. We have a deadline of
9:05. The actual time was 9:12. so I have 9:05 in cell B6 and I have
9:12 in cell C6. In D6 I want to show that we were 7 minutes
late...preferably that would show -7.

If I use the formula in D6 =MINUTE(c6-b6) The result is 7. Ideally I
would like fo that to be -7.

If we were actually ahead of deadline, say 9:00 a.m. the formula
returns #NUM! and I would like to show a positive number.

If you use the formula =24*60*(B6-C6) you'll get the -7 and +5 answers that
you want. [Format the result as General, not as a Time, as the answer is
the number of minutes.]
--
David Biddulph

Scoops,

You are right about the plus minutes.

I changed to the 1904, but I still get the num error when we are early
rather than late.

I had already tried the other suggestion of 1440 and that doesn't seem
to work.

I really didn't understand your comment "Note that this will change any
dates that already exist in your sheet
and you will need to amend them." What will I need to amend?

Thanks,

Jon


--
jonhunt
------------------------------------------------------------------------
jonhunt's Profile: http://www.excelforum.com/member.php...o&userid=35370
View this thread: http://www.excelforum.com/showthread...hreadid=557829

Perfect. Thanks.


--
jonhunt
------------------------------------------------------------------------
jonhunt's Profile: http://www.excelforum.com/member.php...o&userid=35370
View this thread: http://www.excelforum.com/showthread...hreadid=557829

jonhunt wrote:

Scoops,

You are right about the plus minutes.

I changed to the 1904, but I still get the num error when we are early
rather than late.

I had already tried the other suggestion of 1440 and that doesn't seem
to work.

I really didn't understand your comment "Note that this will change any
dates that already exist in your sheet
and you will need to amend them." What will I need to amend?

Hi Jon

Not sure why your sheet isn't performing, if I toggle the 1904 date
system, I get the right answer or a cell full of #.

As for the amendments - try typing a date into Excel under the default
1900 and then use 1904. You should see the date shift by 1462 days so,
to see it as it was before the change you'd amend it by the same (i.e.
thedate-1462). Any new dates can be entered "as is".

Regards

Steve

jonhunt wrote:

Scoops,

I had already tried the other suggestion of 1440 and that doesn't seem
to work.

Hi Jon

Both David's and daddylonglegs solutions work fine for me, though I did
amend them to
=24*60*-(B6-C6)
and
=(B6-C6)*-1440
to change the +/-

Is your data ok?

Regards

Steve