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

[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

 

[Claus Busch]

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.

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.

 

[oldyork90]

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

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.

 

[Claus Busch]

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).

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


Regards
Claus B.

 

[joeu2004]

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

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).

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

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.

 

[Claus Busch]

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(!).

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.

 

[joeu2004]

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

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.

 

[Claus Busch]

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.)

thank you for that excellent explanation.


Regards
Claus B.

 

[joeu2004]

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.

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

(And surprisingly so.)

 

[Claus Busch]

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.

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.

 

[joeu2004]

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

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.

 

[joeu2004]

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

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.

 

[Claus Busch]

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:

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


Regards
Claus B.