Wrestling with fractions and dates. Knowledge of photography is helpful.

I made a spreadsheet to combine f/stop, shutter speed, and film speed into a 
single number. This number represents how many stops the exposure was taken 
from a sunny 16 setting. For example, if I'm using ISO 100 film, a shutter 
speed of 1/125 sec., and an f/stop of 11, it will calculate to about .8. Ok, 
now for the fun part. When I enter a shutter speed into a cell, I just input 
the denominator because all the numbers are just reciprocals of their 
respective shutter speeds. For example, for 1/125 sec, I enter 125 and let 
the inner workings take care of it. The problem is when the shutter speeds 
are in the 1 second or more neighborhood, I have to be able to differentiate 
between 1/4 of a second and 4 seconds. So I use the same syntax as what the 
camera displays. 4 seconds is displayed as 4". For 7/10 of a second, the 
display shows 0"7. For this type of format, I will just type 7/10 in the 
cell. Here is the part of the equation that is messing up the works:

=IF(ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2)),2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2),2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))

And just to simplify things, here is just the middle part of the expression 
which does the evalutation when the quote sign is present: 
2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2)

F40 is the f/stop
E40 is the shutter speed
C2 is the ISO of the film.

Now when this function evaluates to an error because of the quote sign in 
the cell, it takes the alternate path of extracting the numbers before the 
quote sign. But if the number is 7/10", or 7/10 of a second, Excel is 
interpreting this as a date and the output is not as expected. If I format 
the cell as a fraction, it doesn't matter. I'm not sure why Excel even 
accepts the fraction format when 7/10" isn't a fraction anyway. Any ideas 
how to set this matter straight?

Thanks for your replies.

David Farber
L.A., CA


0
David
11/17/2009 1:07:55 AM
excel.programming 6508 articles. 2 followers. Follow

7 Replies
821 Views

Similar Articles

[PageSpeed] 46

On Mon, 16 Nov 2009 17:07:55 -0800, "David Farber" <farberbear.unspam@aol.com>
wrote:

>I made a spreadsheet to combine f/stop, shutter speed, and film speed into a 
>single number. This number represents how many stops the exposure was taken 
>from a sunny 16 setting. For example, if I'm using ISO 100 film, a shutter 
>speed of 1/125 sec., and an f/stop of 11, it will calculate to about .8. Ok, 
>now for the fun part. When I enter a shutter speed into a cell, I just input 
>the denominator because all the numbers are just reciprocals of their 
>respective shutter speeds. For example, for 1/125 sec, I enter 125 and let 
>the inner workings take care of it. The problem is when the shutter speeds 
>are in the 1 second or more neighborhood, I have to be able to differentiate 
>between 1/4 of a second and 4 seconds. So I use the same syntax as what the 
>camera displays. 4 seconds is displayed as 4". For 7/10 of a second, the 
>display shows 0"7. For this type of format, I will just type 7/10 in the 
>cell. Here is the part of the equation that is messing up the works:
>
>=IF(ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2)),2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2),2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))
>
>And just to simplify things, here is just the middle part of the expression 
>which does the evalutation when the quote sign is present: 
>2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2)
>
>F40 is the f/stop
>E40 is the shutter speed
>C2 is the ISO of the film.
>
>Now when this function evaluates to an error because of the quote sign in 
>the cell, it takes the alternate path of extracting the numbers before the 
>quote sign. But if the number is 7/10", or 7/10 of a second, Excel is 
>interpreting this as a date and the output is not as expected. If I format 
>the cell as a fraction, it doesn't matter. I'm not sure why Excel even 
>accepts the fraction format when 7/10" isn't a fraction anyway. Any ideas 
>how to set this matter straight?
>
>Thanks for your replies.
>
>David Farber
>L.A., CA
>

David,

I don't really understand your formula.  But one way to ensure that the value
in E40 is properly converted would be to use a User Defined Function, at least
for that part of the formula.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

You can then use Eval(E40) wherever you are evaluated E40 for a value.

You might be able to simplify your function to:

=IF(ISERROR(-E40),
2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2),
2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))

=================================
Function Eval(s As String) As Double
    Eval = Evaluate(Replace(s, """", ""))
End Function
====================================



--ron
0
Ron
11/17/2009 2:52:54 AM
Ron Rosenfeld wrote:
> On Mon, 16 Nov 2009 17:07:55 -0800, "David Farber"
> <farberbear.unspam@aol.com> wrote:
>
>> I made a spreadsheet to combine f/stop, shutter speed, and film
>> speed into a single number. This number represents how many stops
>> the exposure was taken from a sunny 16 setting. For example, if I'm
>> using ISO 100 film, a shutter speed of 1/125 sec., and an f/stop of
>> 11, it will calculate to about .8. Ok, now for the fun part. When I
>> enter a shutter speed into a cell, I just input the denominator
>> because all the numbers are just reciprocals of their respective
>> shutter speeds. For example, for 1/125 sec, I enter 125 and let the
>> inner workings take care of it. The problem is when the shutter
>> speeds are in the 1 second or more neighborhood, I have to be able
>> to differentiate between 1/4 of a second and 4 seconds. So I use the
>> same syntax as what the camera displays. 4 seconds is displayed as
>> 4". For 7/10 of a second, the display shows 0"7. For this type of
>> format, I will just type 7/10 in the cell. Here is the part of the
>> equation that is messing up the works:
>>
>> =IF(ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2)),2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2),2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))
>>
>> And just to simplify things, here is just the middle part of the
>> expression which does the evalutation when the quote sign is present:
>> 2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2)
>>
>> F40 is the f/stop
>> E40 is the shutter speed
>> C2 is the ISO of the film.
>>
>> Now when this function evaluates to an error because of the quote
>> sign in the cell, it takes the alternate path of extracting the
>> numbers before the quote sign. But if the number is 7/10", or 7/10
>> of a second, Excel is interpreting this as a date and the output is
>> not as expected. If I format the cell as a fraction, it doesn't
>> matter. I'm not sure why Excel even accepts the fraction format when
>> 7/10" isn't a fraction anyway. Any ideas how to set this matter
>> straight?
>>
>> Thanks for your replies.
>>
>> David Farber
>> L.A., CA
>>
>
> David,
>
> I don't really understand your formula.  But one way to ensure that
> the value in E40 is properly converted would be to use a User Defined
> Function, at least for that part of the formula.
>
> To enter this User Defined Function (UDF), <alt-F11> opens the Visual
> Basic Editor.
> Ensure your project is highlighted in the Project Explorer window.
> Then, from the top menu, select Insert/Module and
> paste the code below into the window that opens.
>
> You can then use Eval(E40) wherever you are evaluated E40 for a value.
>
> You might be able to simplify your function to:
>
> =IF(ISERROR(-E40),
> 2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2),
> 2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))
>
> =================================
> Function Eval(s As String) As Double
>    Eval = Evaluate(Replace(s, """", ""))
> End Function
> ====================================
>
>
>
> --ron

Hi Ron,

I really don't understand the ISERROR condition, -E40. Is that some trick to 
test if there is a number in the cell?

I entered the code, =2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2) in a 
cell along with 2" in E40 and 13 in F40. I then entered your three lines of 
VB code in the VB editor. The result in the cell is now, #NAME?

I'm not sure how to proceed from here.

Thanks for your reply.

--
David Farber
L.A., CA 


0
David
11/17/2009 4:17:45 PM
David Farber wrote:
> Ron Rosenfeld wrote:
>> On Mon, 16 Nov 2009 17:07:55 -0800, "David Farber"
>> <farberbear.unspam@aol.com> wrote:
>>
>>> I made a spreadsheet to combine f/stop, shutter speed, and film
>>> speed into a single number. This number represents how many stops
>>> the exposure was taken from a sunny 16 setting. For example, if I'm
>>> using ISO 100 film, a shutter speed of 1/125 sec., and an f/stop of
>>> 11, it will calculate to about .8. Ok, now for the fun part. When I
>>> enter a shutter speed into a cell, I just input the denominator
>>> because all the numbers are just reciprocals of their respective
>>> shutter speeds. For example, for 1/125 sec, I enter 125 and let the
>>> inner workings take care of it. The problem is when the shutter
>>> speeds are in the 1 second or more neighborhood, I have to be able
>>> to differentiate between 1/4 of a second and 4 seconds. So I use the
>>> same syntax as what the camera displays. 4 seconds is displayed as
>>> 4". For 7/10 of a second, the display shows 0"7. For this type of
>>> format, I will just type 7/10 in the cell. Here is the part of the
>>> equation that is messing up the works:
>>>
>>> =IF(ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2)),2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2),2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))
>>>
>>> And just to simplify things, here is just the middle part of the
>>> expression which does the evalutation when the quote sign is
>>> present:
>>> 2*LOG(16/F40)/LOG(2)+LOG($C$2*(MID(E40,1,FIND("""",E40)-1)))/LOG(2) F40 
>>> is the f/stop
>>> E40 is the shutter speed
>>> C2 is the ISO of the film.
>>>
>>> Now when this function evaluates to an error because of the quote
>>> sign in the cell, it takes the alternate path of extracting the
>>> numbers before the quote sign. But if the number is 7/10", or 7/10
>>> of a second, Excel is interpreting this as a date and the output is
>>> not as expected. If I format the cell as a fraction, it doesn't
>>> matter. I'm not sure why Excel even accepts the fraction format when
>>> 7/10" isn't a fraction anyway. Any ideas how to set this matter
>>> straight?
>>>
>>> Thanks for your replies.
>>>
>>> David Farber
>>> L.A., CA
>>>
>>
>> David,
>>
>> I don't really understand your formula.  But one way to ensure that
>> the value in E40 is properly converted would be to use a User Defined
>> Function, at least for that part of the formula.
>>
>> To enter this User Defined Function (UDF), <alt-F11> opens the Visual
>> Basic Editor.
>> Ensure your project is highlighted in the Project Explorer window.
>> Then, from the top menu, select Insert/Module and
>> paste the code below into the window that opens.
>>
>> You can then use Eval(E40) wherever you are evaluated E40 for a
>> value. You might be able to simplify your function to:
>>
>> =IF(ISERROR(-E40),
>> 2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2),
>> 2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))
>>
>> =================================
>> Function Eval(s As String) As Double
>>    Eval = Evaluate(Replace(s, """", ""))
>> End Function
>> ====================================
>>
>>
>>
>> --ron
>
> Hi Ron,
>
> I really don't understand the ISERROR condition, -E40. Is that some
> trick to test if there is a number in the cell?
>
> I entered the code,
> =2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2) in a cell along
> with 2" in E40 and 13 in F40. I then entered your three lines of VB
> code in the VB editor. The result in the cell is now, #NAME?
> I'm not sure how to proceed from here.
>
> Thanks for your reply.

Something happened after I gave up trying to figure out the code. I loaded 
an unrelated spreadsheet on top of the one in question. Then I closed the 
unrelated one. Now the #NAME? error is gone and I think the correct numeric 
value is displayed. Was there some extra step I was supposed to do after 
entering the VB code to get it to run?

Thanks for your reply.

--
David Farber
L.A., CA 


0
David
11/17/2009 4:29:33 PM
On Tue, 17 Nov 2009 08:29:33 -0800, "David Farber" <farberbear.unspam@aol.com>
wrote:

>Something happened after I gave up trying to figure out the code. I loaded 
>an unrelated spreadsheet on top of the one in question. Then I closed the 
>unrelated one. Now the #NAME? error is gone and I think the correct numeric 
>value is displayed. Was there some extra step I was supposed to do after 
>entering the VB code to get it to run?
>
>Thanks for your reply.

Just entering the VBA code will not trigger a calculation event.  So the #NAME!
error you see before entering the VBA code will persist until you do something
that triggers a calculation event.
--ron
0
Ron
11/17/2009 9:45:54 PM
Ron Rosenfeld wrote:
> On Tue, 17 Nov 2009 08:29:33 -0800, "David Farber"
> <farberbear.unspam@aol.com> wrote:
>
>> Something happened after I gave up trying to figure out the code. I
>> loaded an unrelated spreadsheet on top of the one in question. Then
>> I closed the unrelated one. Now the #NAME? error is gone and I think
>> the correct numeric value is displayed. Was there some extra step I
>> was supposed to do after entering the VB code to get it to run?
>>
>> Thanks for your reply.
>
> Just entering the VBA code will not trigger a calculation event.  So
> the #NAME! error you see before entering the VBA code will persist
> until you do something that triggers a calculation event.
> --ron

Hi Ron,

I must have changed the data in one of the other cells to trigger that.

I'm still curious to know the theory behind the (-E40) of the ISERROR code:
=IF(ISERROR(-E40),
2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2),
2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))

I was thinking that putting a quote mark in a division problem i.e.: 100/2"
would trigger the error and force it to convert the string to a number. Is
your shortcut saying that you can't take the opposite of 2" (for example)
because it's not a number, thus triggering the error condition?

Thanks for your reply.
--
David Farber
L.A., CA


0
David
11/18/2009 6:41:52 PM
On Wed, 18 Nov 2009 10:41:52 -0800, "David Farber" <farberbear.unspam@aol.com>
wrote:

>I'm still curious to know the theory behind the (-E40) of the ISERROR code:
>=IF(ISERROR(-E40),
>2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2),
>2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))
>
>I was thinking that putting a quote mark in a division problem i.e.: 100/2"
>would trigger the error and force it to convert the string to a number. Is
>your shortcut saying that you can't take the opposite of 2" (for example)
>because it's not a number, thus triggering the error condition?

Your original error test was:

ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))

to decide which branch of the IF statement to take.  But this error condition
really only depends on E40 being text or a number. So that is really the only
cell you need to check.

Performing an arithmetic operation on a value will produce an error if the
value is not numeric.  So that's what I did by "negating" the value.

One could consider using ISTEXT or ISNUMBER, but if the value is TEXT, but
Excel could interpret the value as a number, this might give undesired results,
in your particular situation where the value will either be or not be followed
by a quote.

It is certainly true that your initial error statement will return a useful
result, but I prefer shorter statements when appropriate.
--ron
0
Ron
11/18/2009 9:33:44 PM
Ron Rosenfeld wrote:
> On Wed, 18 Nov 2009 10:41:52 -0800, "David Farber"
> <farberbear.unspam@aol.com> wrote:
>
>> I'm still curious to know the theory behind the (-E40) of the
>> ISERROR code: =IF(ISERROR(-E40),
>> 2*LOG(16/F40)/LOG(2)+LOG($C$2*(Eval(E40)))/LOG(2),
>> 2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))
>>
>> I was thinking that putting a quote mark in a division problem i.e.:
>> 100/2" would trigger the error and force it to convert the string to
>> a number. Is your shortcut saying that you can't take the opposite
>> of 2" (for example) because it's not a number, thus triggering the
>> error condition?
>
> Your original error test was:
>
> ISERROR(2*LOG(16/F40)/LOG(2)+LOG($C$2/E40)/LOG(2))
>
> to decide which branch of the IF statement to take.  But this error
> condition really only depends on E40 being text or a number. So that
> is really the only cell you need to check.
>
> Performing an arithmetic operation on a value will produce an error
> if the value is not numeric.  So that's what I did by "negating" the
> value.
>
> One could consider using ISTEXT or ISNUMBER, but if the value is
> TEXT, but Excel could interpret the value as a number, this might
> give undesired results, in your particular situation where the value
> will either be or not be followed by a quote.
>
> It is certainly true that your initial error statement will return a
> useful result, but I prefer shorter statements when appropriate.
> --ron

That's quite slick and compact!

Thanks.

--
David Farber
L.A., CA 


0
David
11/20/2009 7:42:29 PM
Reply:

Similar Artilces:

Easiest way to get 3rd col = monthly averages, from cols 1, 2 = weekly date, data?
Hi all, I've got years of data of the following sort: 1/2/98, 55.0 1/9/98, 53.2 1/16/98, 43.5 1/23/98, 45.2 1/30/98, 64.3 .. .. .. (and so on, up to the present) I would like a 3rd column, with an entry on each row representing the last week of a month, whose value is the average of the data points for that month. Note that the number of weeks in a month varies. Greatly appreciating any quick & easy approaches, cdj Hi Rather than trying to do it on all the rows that are the last week of a given month, in column D starting at D2 enter 01/01/98 Format>Cells>Number>Cust...

Populating the last Sun. date of that week in a cell
Thanks in advance. I'm using Word 2003 & I'd like to populate a cell of a table to fill in the last Sunday-DATE of that week. This is for a timesheet.doc where that date needs to be filled in for our Payroll Dept. Currently folks are having to go to a calendar & look up in order to manually populate this field. Many thanks, MC Thanks Graham, I recognize this as VBA, but don't know where/how to do this. I think if you give me simple instructions I can navigate through it enough. Also just to clarify, what I'm after is (w/ today being the 27th) I'...

Change Tracking Help?
I have a table with a varchar column and datetimeoffset column. SQL Server 2008 (not R2) Change Tracking is enabled on the database and the table and column-tracking is enabled. On one DB connection, I inserted 3 rows. On a different connection, I updated 1 row's varchar and datetimeoffset. I want to know which row was updated so I can take some action. The below SQL shows all 3 rows as "updated', I expected I guess 3 inserts and 1 update. SELECT CT.ItemsId, P.Stamp, P.ItemName, CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT ...

whole number fractions to proper fractions
MS Word 2004 As a cookbook editor, I receive countless submissions with the most common fractions entered as whole numbers. I have quick correct to convert these if I am typing them myself however I need a way to have Word make the conversion on a per document basis rather than on a manual fraction by fraction basis or even using Find/Replace as I do now. In the old WordPerfect days, running spell check would do this as well as convert generic quotes to typographer's quotes; Word does neither. Google has not been my friend in this search. Appropriate suggestions from this for...

Changing Budget Dates
I like to track my budget on Microsoft Money 2003. I have set it up to give me budget figures based on the "current month". I want to change the current month setting to start on the 15th (payday) instead of the 1st. Any clue as to how to do this? Yes. Redesign/rewrite the application. Money doesn't allow you to do this as it presently exists. "Elizabeth" <anonymous@discussions.microsoft.com> wrote in message news:08f001c39382$52ae1dc0$a301280a@phx.gbl... > I like to track my budget on Microsoft Money 2003. I have > set it up to give me budget figures ...

Excel 2003 help
hi all gurus, below is the code to attach the excel file in an email, it is only attaching one sheet within the document. How do i get it to attach the entire workbook? Option Explicit Sub Button66_Click() Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim Response As String Dim DefaultFolder As String, DefaultFileName As String Dim FileToSave Dim OutApp As Object Dim OutMail As Object Dim strbody As String Response = MsgBox("Are you sure you want to submit this to ...

Formula help... #3
In column A there are cells with team names in them like the following: NYY NYY NYY @OAK @OAK @OAK BOS BOS BOS @TEX @TEX @TEX I would like column B to list all the team names without the @ symbol and column H to list all the teams with the @ symbol. For the example I would like column B to have NYY NYY NYY BOS BOS BOS =COUNTIF(A:A,"@*") and =COUNTIF(A:A,"<>@*") -- HTH RP (remove nothere from the email address if mailing direct) "Jambruins" <Jambruins@discussions.microsoft.com> wrote in message news:135965C4-7523-47AB-88DE-D0B12B652CD5@m...

viewing attachments inline....HELP!!
I've got a real quandary here. I've just upgraded to Office 2000, and when in Outlook, I can't view any attachments inline (they usually come in as attachments). In Outlook Express, they were attached as well as inline. Now Outlook doesn't do that, and there is no tab anywhere in the Options, etc as to be able to check that attachments my be viewed inline. On my MAC (running Office v.x), Entourage DOES have that option. Is this lack of inline viewing "the nature of the beast" for 2000?? I haven't had any luck searching the KBs... Any input would be greatly appre...

email date stamp
hello all Can you tell me whether there is anyway to change the Outlook email stamp to a different format All emails from everyone all seem to have the following format Sent: Wednesday, August 23, 2006 6:10 PM for recording when the email was sent. All emails I send use the same format. A colleague of mine has told me that he can make Outlook stamp all his outgoing emails with the following format: Sent: 2006-08-21 05:28 Can individuals control this? if so how? In your display? View-> Arrange By-> Current View-> Customize Current View-> Format Columns... -- Robert Sparn...

Fraction Formatting
I am creating an employee time sheet, where the hours worked will be entered in increments as small as 15 minutes and be displayed as fractions. For example, I would like 2.5 hours to display as "2 1/2" and 4.25 hours to display as "4 1/4". When I choose "Fractions" in the cell formatting and select the fraction denominator as '4', 2.5 hours displays as 2 2/4, not 2 1/2. How do I set the formatting to display as I want? TIA David David just format it as "Up to two digits (21/25)" Regards Trevor "David Rose" <drose@com...

outlook prints date footers
Hi, While printing MS Outlook mails using visual basic code, it prints with date footers for HTML mails (only for html). We cleared the headers and footers of outlook and IE print page setup. But also it shows the same problem. So please suggest a solution for this. Thanks & Regards, Babu ...

Mem_Bad_Handle Please Help!
Hi, I have a Mem_bad_handle issue. I realise I dont have a reports.dic and forms.dic. The reason I dont have it is because I dont have the opportunity to run modifier and report writer yet. I can't run the mentioned apps because when I use Utilities for the first time I always get the above error. Please help. Are you running GP remotely? "xs" <blizzardstorm8899@yahoo.com.sg> wrote in message news:OAXm3d%23ZEHA.3112@tk2msftngp13.phx.gbl... > Hi, > > I have a Mem_bad_handle issue. I realise I dont have a reports.dic and > forms.dic. The reason I dont have i...

date extraction
I have a macro set up that pulls out the dates from my information. I am not sure but I think what I have below is the spot that defines the date format to pull out my dates from my data. If I put 2/2/10 it does not pull out the date. If I put 02 it does pull out the date. Is there a way so it will pull it out whether I put 2 or 02 for the month? Selection.NumberFormat = "m/d/yy;@" Thank You Get it as a String and then convert to a Date: Sub luxation() Dim s As String, d As Date s = Selection.Text d = DateValue(s) MsgBox d End Sub -- Gary'&...

Subtracting dates to get hours... but I want to skip weekends
In column A I have start times (DD/MM/YY HH/MM/SS) and in column B I have the stop times. I am measuring the hours from the difference of these columns for each row, in column C. However, I would like to exclude weekends from the subtraction. I.e Friday 5 pm is the start time, Monday 8 am is the stop time, then the difference would be 15 hours instead of 63 hours. I know how to convert the fractions returned from the difference, into hours/minutes/days, and I also know how to convert dates to "day of week", so unless you guys know of a good idea I was just going to sort the file...

re:error in installing kb955706 from vista up date
any solution about the error so that it can be install successfully what error? arthur wrote: > any solution about the error so that it can be install successfully Arthur, Please always state which Windows edition and Service Pack level ? and what version of Internet Explorer? as it often does make a difference - Also, could you supply some information on what your firewall is, what is the installed antivirus/security suite, other R-TP programs, e.g. Spybot Search & Destroy (reduced functionality called "TeaTimer") Avira (From version 9) (Included in anti...

Default Value =Date$() in UK Date Format
I have a form with a date field and want to populate it with the current date using the =Date$() in the default value field. Unfortunately this uses the US date format (mmddyy) and I need it to be in the UK format (ddmmyy). Regional settings are set to UK on the PC. Can anyone please advise me how to acheive this as I can't find any reference to it anywhere on the web! =Date() Should use your regional setting Jon "Shugwan" <Shugwan@discussions.microsoft.com> wrote in message news:C07ACAC6-6139-4667-B839-89D386B05EA0@microsoft.com... >I have a for...

Error in Replying All to email in History in CRM 3.0
My client was trying to create a 'Reply All' to an email in history, the email having many recipients in it. So my client just clicked Reply to All and typed her message and sent it, but had this error: Duplicate Record: A record with these values already exists. A duplicate record cannot be created. Select one or more unique values and try again. FYI, when forwarding there is no error. Environment: SBS 2003 Email is working perfectly fine for other CRM tasks. They can attach files and everything. Only in this instance they are having error. ...

Updating from M2001 to M2005...need help!
I currently have M2001 and want to update to M2005. If I do this will is automatically update/transfer my info into 2005. If not how can it be done? Also, has there been many problems doing this? It should do, so long as the versions are from the same country, eg UK->UK or US->US. There can be problems, but the easiest way to find out is to get the trial version for the version you require. -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny or http://money.mvps.org/faq for UK tips and fixes for MS Money. ...

Importing dates from text file
I have counter data from a windows 2000 servers which I am trying t graph. The text file has data in the format below. "(PDH-CSV 4.0) (GMT Dayligh Time)(-60)","\\SERVER1\Memory\Pages/sec","\\SERVER1\PhysicalDisk(_Total)\Avg Disk Queue Length","\\SERVER1\Processor(_Total)\% Processor Time" "03/31/200 23:59:41.115","0","0.0012533333333333333","0.52083333333333703" "03/31/200 23:59:56.115","0","0.0020799999999999998","1.041666666666663" "04/01/2004 00:00:11.115",&quo...

Help on CListCtrl
Hi, Iam using the Report view of CListCtrl. I have to sort the rows based on the values of a column which the user clicks. When the user clicks on the column header the first time, it should rearrange the rows in ascending order of the column values. When the user clicks on the column header the second time, it should rearrange the rows in descending order. Now, the following piece of code works perfectly to rearrange the rows in ascending order when I click the column header for the first time. But it doesnt rearrange them in descending order when I click on the column header th...

Using NOW() in SQL for date fields
I am just a newbie when it comes to SQL, but I am very amazed with its features. In a default mdb database I use "NOW" to filter a query with a datefield. Like: SELECT * FROM tblOrders Where (((tblOrders.OrderDate) Between now() And now()+3)) But in SQL this does not seems to work, how can I use the now statement in SQL? -- ���`�.(*�.�(`�.� �.��)�.�*).��`�� �.............. CHARLIE ..............� ���`�.(�.��(�.�* *�.�)`�.�).��`�� If you want to filter the records by date without the time then it's better to use Date() that return the date, when Now return ...

Recommended Currency Expiration Dates
In our testing of Multi-Currency, we were unable to post some A/R transactions as we got an error message of: "Error: The Exchange rate has expired." We are currently importing Currency Rate data on a daily basis from the Federal Reserve website. As such, should we make the expiration date say 10-30 days from the Currency Date? I presume that if we do that, the rate which will be used will be the closest to the Invoice date. Is there any downside to having an Expiration date overlap the life of another currency? (i.e. if We have a Currency Rate for 10/31 expiring on 11...

Investor's Knowledge Analysis
Investor's Knowledge Analysis Hello! Please fill out the survey on Investment awareness & knowledge analysis. -- http://spreadsheets.google.com/viewform?key=pb_z4f1_zGMhaGMOzo4IsPw&email=true Check out this neat website I came across. You can find it here: http://investorline.co.in/blogs/news/?p=850 Thanks for reading! Investorline Services ...

Need Help With Files
I'm not sure if this is the right forum, but here goes: I have an excel file that runs several macros (lets call it File1). need to create another file that will have "text" information tha depends on the day of the month (lets call it File2). I'd like to create macro or VB script that will copy data from File and put it in File1. Even better and heres my ultimate dream: when the data in file2 goes t file1 it is in 'Comment' form... Can anyone help -- Message posted from http://www.ExcelForum.com Here's my code: Windows("Deviation.xls").Activate ...

How do I convert dd/mm/yy date format to yyddd Julian date format
I have done this before, but can't remember how! =TEXT(A1,"yy")&TEXT(A1-DATE(YEAR(A1),1,0),"000") -- David Biddulph "itzy bitzy" <itzybitzy@discussions.microsoft.com> wrote in message news:D05271B8-8D0F-44D7-BF53-CCD8C5F45234@microsoft.com... >I have done this before, but can't remember how! Format cells > Custom > and in "Type" enter: yyyy dd mm or whatever you'd like "itzy bitzy" wrote: > I have done this before, but can't remember how! Hi, with custom format, choose the range to...