Time Diff from text format

[JICDB]

I have searched for quite some time but can't seem to find the same problem.
I have a file containing 50,000 lines containing scheduled times and actual
times but the catch is that the original source formats the time as 630 for
6:30 or 1350 for 1:50pm. I am trying to subtract the actual time from the
scheduled time to determine how many minutes the bus was late.

I tried adding leading zeros and subtracting the left 2 digits from each
other, but that didn't work. I tried a variey of formulas listed here but
none worked. Any ideas?

 

[JE McGimpsey]

One way:

=TEXT(B1,"00\:00")-TEXT(A1,"00\:00")

or, if the times may span midnight,

=MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)

 

[JICDB]

This didn't work for some reason. Here's my example:
The bus was supposed to stop at 619 (actual number in spreadsheet) but
actually picked up at 632. The difference should be -13 but this formula
give me -0.009027777. Did I do something wrong?

 

[David Biddulph]

Yes, you did something wrong. you've got the cell formatted as a number (a
fraction of a day), rather than as time. A suitable format would be
[h]:mm:ss.
You may then find you've got problems because Excel doesn't usually like
working with negative times, so you'll probably have to use Tools/ Options/
Calculation:
1904 date system (but you'll need to be careful if you transfer data to &
from between 1904 and 1900 systems).

 

[Gord Dibben]

Format the cell as Custom h:mm

BTW: B1 contains 632 and A1 contains 619

Negative times will give you an error of ############# so don't bother trying to
subtract 632 from 619


Gord Dibben MS Excel MVP

 

[JICDB]

You are a genius and I bow to you!! It worked like a charm. Thanks!!!

Yes, you did something wrong. you've got the cell formatted as a number (a
fraction of a day), rather than as time. A suitable format would be
[h]:mm:ss.
You may then find you've got problems because Excel doesn't usually like
working with negative times, so you'll probably have to use Tools/ Options/
Calculation:
1904 date system (but you'll need to be careful if you transfer data to &
from between 1904 and 1900 systems).
--
David Biddulph

This didn't work for some reason. Here's my example:
The bus was supposed to stop at 619 (actual number in spreadsheet) but
actually picked up at 632. The difference should be -13 but this formula
give me -0.009027777. Did I do something wrong?

 

[JICDB]

Ok these people are never happy - now they tell me that what they want is the
minutes in hundreds. So instead of a different of -1.30 for one hour and
thirty minutes they want -1.5 hours. Can this formula be adjusted to
accomodate this request?

Yes, you did something wrong. you've got the cell formatted as a number (a
fraction of a day), rather than as time. A suitable format would be
[h]:mm:ss.
You may then find you've got problems because Excel doesn't usually like
working with negative times, so you'll probably have to use Tools/ Options/
Calculation:
1904 date system (but you'll need to be careful if you transfer data to &
from between 1904 and 1900 systems).
--
David Biddulph

This didn't work for some reason. Here's my example:
The bus was supposed to stop at 619 (actual number in spreadsheet) but
actually picked up at 632. The difference should be -13 but this formula
give me -0.009027777. Did I do something wrong?

 

[David Biddulph]

If you were getting -0.009027777 for your -13 minutes, leave it as format
general or number, rather than time, and multiply it by 24 and that will
give you an answer in hours. If you want it in minutes, multiply the hours
by 60.
--
David Biddulph

Ok these people are never happy - now they tell me that what they want is
the
minutes in hundreds. So instead of a different of -1.30 for one hour and
thirty minutes they want -1.5 hours. Can this formula be adjusted to
accomodate this request?

Yes, you did something wrong. you've got the cell formatted as a number
(a
fraction of a day), rather than as time. A suitable format would be
[h]:mm:ss.
You may then find you've got problems because Excel doesn't usually like
working with negative times, so you'll probably have to use Tools/
Options/
Calculation:
1904 date system (but you'll need to be careful if you transfer data to &
from between 1904 and 1900 systems).
--
David Biddulph

This didn't work for some reason. Here's my example:
The bus was supposed to stop at 619 (actual number in spreadsheet) but
actually picked up at 632. The difference should be -13 but this
formula
give me -0.009027777. Did I do something wrong?

:

One way:

=TEXT(B1,"00\:00")-TEXT(A1,"00\:00")

or, if the times may span midnight,

=MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)

I have searched for quite some time but can't seem to find the same
problem.
I have a file containing 50,000 lines containing scheduled times and
actual
times but the catch is that the original source formats the time as
630
for
6:30 or 1350 for 1:50pm. I am trying to subtract the actual time
from
the
scheduled time to determine how many minutes the bus was late.

I tried adding leading zeros and subtracting the left 2 digits from
each
other, but that didn't work. I tried a variey of formulas listed
here
but
none worked. Any ideas?

 

[JICDB]

Thanks again! It worked great. Now they'll want it in a foreign language or
something!

If you were getting -0.009027777 for your -13 minutes, leave it as format
general or number, rather than time, and multiply it by 24 and that will
give you an answer in hours. If you want it in minutes, multiply the hours
by 60.
--
David Biddulph

Ok these people are never happy - now they tell me that what they want is
the
minutes in hundreds. So instead of a different of -1.30 for one hour and
thirty minutes they want -1.5 hours. Can this formula be adjusted to
accomodate this request?

Yes, you did something wrong. you've got the cell formatted as a number
(a
fraction of a day), rather than as time. A suitable format would be
[h]:mm:ss.
You may then find you've got problems because Excel doesn't usually like
working with negative times, so you'll probably have to use Tools/
Options/
Calculation:
1904 date system (but you'll need to be careful if you transfer data to &
from between 1904 and 1900 systems).
--
David Biddulph

This didn't work for some reason. Here's my example:
The bus was supposed to stop at 619 (actual number in spreadsheet) but
actually picked up at 632. The difference should be -13 but this
formula
give me -0.009027777. Did I do something wrong?

:

One way:

=TEXT(B1,"00\:00")-TEXT(A1,"00\:00")

or, if the times may span midnight,

=MOD(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"),1)

I have searched for quite some time but can't seem to find the same
problem.
I have a file containing 50,000 lines containing scheduled times and
actual
times but the catch is that the original source formats the time as
630
for
6:30 or 1350 for 1:50pm. I am trying to subtract the actual time
from
the
scheduled time to determine how many minutes the bus was late.

I tried adding leading zeros and subtracting the left 2 digits from
each
other, but that didn't work. I tried a variey of formulas listed
here
but
none worked. Any ideas?