Re: Time Diff from text format

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

On Mon, 5 Mar 2007 09:36:18 -0800, JICDB <JICDB@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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?

"JE McGimpsey" wrote:

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)

In article <576958BC-9ED8-4454-887E-009EB279C94F@xxxxxxxxxxxxx>,
JICDB <JICDB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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?


.

Relevant Pages

  • Re: Time Diff from text format
    ... If you were getting -0.009027777 for your -13 minutes, leave it as format ... David Biddulph ... I have a file containing 50,000 lines containing scheduled times and ... scheduled time to determine how many minutes the bus was late. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Time Diff from text format
    ... A suitable format would be ... I have a file containing 50,000 lines containing scheduled times and ... scheduled time to determine how many minutes the bus was late. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: fixed-point
    ... point representation. ... To add or subtract two fixed point numbers, ... You wnat the result in fixed point format. ... than floating point, but not on modern machines because the floating point ...
    (comp.lang.c)
  • Re: Find in a date range
    ...    Set Field [DateField, ... Field above has to work out the current month and subtract 1. ... Captain) instead of a year month day or day month year format. ... But theDatefunction must have it's parameters in American order. ...
    (comp.databases.filemaker)
  • Re: Negative numbers
    ... is either 0 or 1, representing the absence or presence ... I meant to say in my last post that you can replace "subtract" by ... \ convert from normal to NZ format ... \ Then all you have to do is to convert all of the maths operations to ...
    (comp.lang.forth)