Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
Hi,
Am Tue, 28 Jan 2014 08:26:54 -0800 (PST) schrieb : 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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
wrote:
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). "Claus Busch" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
"Claus Busch" wrote:
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 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
Errata.... I wrote:
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.) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
"Claus Busch" wrote:
=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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
PS.... "Claus Busch" wrote:
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
single cell time entry as time - time; Ever seen this?
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with calculating time and a half - I've got straight time and double time. | Excel Discussion (Misc queries) | |||
Time calculations for Scheduled Time vs. Actual Time Worked | Excel Discussion (Misc queries) | |||
Time entry auto changes to date and then time ? | Excel Worksheet Functions | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
Userform for time entry: multiple controls/single value? | Excel Programming |