Calculating time difference in minutes

[jonhunt]

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.

[Scoops]

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

[daddylonglegs]

Use this formula in D6

=(B6-C6)*1440

[jonhunt]

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

[David Biddulph]

"jonhunt" <[email protected]> wrote in
message news:[email protected]...
>
> 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

[jonhunt]

Perfect. Thanks.

[Scoops]

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

[Scoops]

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