single cell time entry as time - time; Ever seen this?

  • Thread starter Thread starter oldyork90
  • Start date Start date
O

oldyork90

Has anyone ever seen time entered as '7:00 - 9:00' in a TIME formatted cell? (It does allow the entry)

VarType returns string. vba attempts to calculate the difference directly fails - which makes sense to me.

I'm guessing I'll have to isolate these values, convert and calculate elapsed time. The noon and midnight rollovers (12hr clock) is also something I have to deal with manually.

Sound right?, - I've just never seen time used like this. Client insists on this format.

Thank you
 
Hi,

Am Tue, 28 Jan 2014 07:18:36 -0800 (PST) schrieb (e-mail address removed):
Has anyone ever seen time entered as '7:00 - 9:00' in a TIME formatted cell? (It does allow the entry)

VarType returns string. vba attempts to calculate the difference directly fails - which makes sense to me.

I'm guessing I'll have to isolate these values, convert and calculate elapsed time. The noon and midnight rollovers (12hr clock) is also something I have to deal with manually.
Click to expand...

if you may not separate these values try it with formula:
=MOD(MID(A1,FIND("- ",A1)+2,99)-LEFT(A1,FIND(" ",A1)-1),1)
The MOD function works with all times but especially with midnight
rollovers


Regards
Claus B.
 
if you may not separate these values try it with formula:

=MOD(MID(A1,FIND("- ",A1)+2,99)-LEFT(A1,FIND(" ",A1)-1),1)

The MOD function works with all times but especially with midnight

rollovers
Click to expand...

mod 1 ... never used it. Thanks for the info
With a time entry of 11:00 - 1:00 I get .58. I wanted just 2 hours (12hr clock). I'll mess with it (maybe copied it wrong). But I'm on my way. Thank you again.
 
Hi,

Am Tue, 28 Jan 2014 08:26:54 -0800 (PST) schrieb (e-mail address removed):
mod 1 ... never used it. Thanks for the info
With a time entry of 11:00 - 1:00 I get .58. I wanted just 2 hours (12hr clock).
Click to expand...

I use 24 hours a day
If you have 11:00 - 13:00 or 11:00 - 1:00 pm it works


Regards
Claus B.
 
Has anyone ever seen time entered as '7:00 - 9:00' in a
TIME formatted cell? (It does allow the entry)
Click to expand...

To explain.... The form "7:00 - 9:00" is interpreted as text. We can
always enter text into a cell, regardless of the numeric format (like Time).


Claus Busch said:
if you may not separate these values try it with formula:
=MOD(MID(A1,FIND("- ",A1)+2,99)-LEFT(A1,FIND(" ",A1)-1),1)
The MOD function works with all times but especially with
midnight rollovers
Click to expand...

Clever! But beware of inherent arithmetic inaccuracies due to the way that
numbers are represented internally (64-bit binary floating-point).

For example, for "6:15 - 7:15" in A1, the MOD result in B1 is displayed as
1:00, but =B1=TIME(1,0,0) returns FALSE(!).

A more-reliable formula would be:

=--TEXT(MOD(MID(A1,FIND("- ",A1)+2,99)-LEFT(A1,FIND(" ",A1)-1),1), "hh:mm")

PS: The unreliability is not limited to using MOD(...,1). The problem
would arise even if we entered 6:15 and 7:15 into separate cells (A1, B1)
and calculated =B1-A1.
 
Hi Joe,

Am Tue, 28 Jan 2014 08:55:31 -0800 schrieb joeu2004:
For example, for "6:15 - 7:15" in A1, the MOD result in B1 is displayed as
1:00, but =B1=TIME(1,0,0) returns FALSE(!).
Click to expand...

that is correct. But if I calculate the time with your formula in C1 and
with mine in B1 and then try C1-B1 I get 0. If I change the format to 30
digits behind the decimal separator all digits are 0


Regards
Claus B.
 
Claus Busch said:
Am Tue, 28 Jan 2014 08:55:31 -0800 schrieb joeu2004:

that is correct. But if I calculate the time with your formula in C1 and
with mine in B1 and then try C1-B1 I get 0. If I change the format to 30
digits behind the decimal separator all digits are 0
Click to expand...

Yet if you format B1 and C1 as Number with 16 decimal places, we see a
difference in the last decimal place. (Generally, it is better to format as
Scientific with 14 decimal places.)

But that is not always the case.

Yes, =C1-B1 is exactly zero. But =C1-B1-0 formatted as Scientific is about
3.47E-17.

The difference is due the dubious "close to zero" heuristic that is vaguely
and poorly described in http://support.microsoft.com/kb/78113.

Excel arbitrarily replaces the arithmetic result with exactly zero when the
arithmetic result is "close to zero".

But even the title "close to zero" is incorrect. For example,
=1E30+1E14-1E30 results in exactly zero, even though 1E14 is not "close to
zero".

Moreover, the application and implementation of the heuristic is
inconsistent. For example, =1E30+1E14-1E30+0, just adding zero, and
=SUM(1E30,1E14,-1E30) result in about 1.40737E+14.

For similar reasons, sometimes =C1=B1 returns TRUE, but =C1-B1=0 returns
FALSE(!).

That is true in your case; even =C1=B1 returns FALSE.

But consider =MOD("6:45"-"6:15",1) in B1 and
=--TEXT(MOD("6:45"-"6:15",1),"hh:mm") in C1.

In that case, =C1=B1 is TRUE, but =C1-B1=0 is FALSE(!). Also, =C1-B1 is
exactly zero, but =C1-B1+0 is about 1.73E-17.
 
Hi Joe,

Am Tue, 28 Jan 2014 12:38:49 -0800 schrieb joeu2004:
Yet if you format B1 and C1 as Number with 16 decimal places, we see a
difference in the last decimal place. (Generally, it is better to format as
Scientific with 14 decimal places.)
Click to expand...

thank you for that excellent explanation.


Regards
Claus B.
 
Errata.... I said:
For similar reasons, sometimes =C1=B1 returns TRUE, but =C1-B1=0 returns
FALSE(!).

That is true in your case; even =C1=B1 returns FALSE.
Click to expand...

Arrgghh! I meant to write: That is __not__ true in your case.

(And surprisingly so.)
 
Hi Joe,

Am Wed, 29 Jan 2014 00:57:10 -0800 schrieb joeu2004:
Arrgghh! I meant to write: That is __not__ true in your case.
Click to expand...

I already understood it correctly

I tested again and the error is not because the time calculation. The
error still comes changing the string to time.

=LEFT(A2,FIND(" -",A2)-1) gives me 7:00 but if I check it against
Time(7,,) I get FALSE.

Another workaround is rounding while changing string to time:
=ROUND(LEFT(A2,FIND(" -",A2)-1)*1440,0)/1440
is exactly Time(7,,)


Regards
Claus B.
 
Claus Busch said:
=LEFT(A2,FIND(" -",A2)-1) gives me 7:00 but if I check it against
Time(7,,) I get FALSE.
Click to expand...

Because in that context, you are comparing the string "7:00" with the number
TIME(7,0,0). Try:

=--LEFT(A2,FIND(" -",A2)-1)

With that formula in B2, =B2-TIME(7,0,0)=0 returns TRUE.

Your original formula was essentially:

=MID(A1,FIND("- ",A1)+2,99)-LEFT(A1,FIND(" ",A1)-1)

In that context, we do not need --MID(...) and --LEFT(...) because __any__
arithmetic operation (subtraction, in this case) is sufficient to convert
the time strings to numeric time.
 
PS.... "Claus Busch said:
Another workaround is rounding while changing string to time:
=ROUND(LEFT(A2,FIND(" -",A2)-1)*1440,0)/1440
is exactly Time(7,,)
Click to expand...

There is some risk of infinitesimal differences. For example:

A2: 7:19 - 8:00
B2: =ROUND(LEFT(A2,FIND(" -",A2)-1)*1440,0)/1440
C2: =--TEXT(LEFT(A2,FIND(" -",A2)-1),"hh:mm")
D2: =B2-TIME(7,19,0)=0
E2: =C2-TIME(7,19,0)=0

D2 is FALSE. E2 is TRUE.
 
Hi Joe,

Am Wed, 29 Jan 2014 01:46:34 -0800 schrieb joeu2004:
Because in that context, you are comparing the string "7:00" with the number
TIME(7,0,0). Try:
Click to expand...

I don't know why I have done this :-(
Sorry, my bad


Regards
Claus B.
 
Back
Top