Calculating time difference in minutes

  • Thread starter Thread starter jonhunt
  • Start date Start date
J

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.
 
jonhunt said:
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.
Click to expand...

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
 
jonhunt said:
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.
Click to expand...

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.]
 
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 said:
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?
Click to expand...

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 said:
Scoops,
Click to expand...
I had already tried the other suggestion of 1440 and that doesn't seem
to work.
Click to expand...

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
 
Back
Top