Formula not working because of decimal separator

[BeRniTo]

Hello again!

I'm getting the "Unable to set FormulaArray property of the Range class" error after runing the following code:

Sub test()
    Range("A1").Value = "05/05/1987 21:00:00"
    Dim timeCompare As Date
    timeCompare = Format("20:00:00", "hh:mm:ss;@")
    
    Range("A2").FormulaArray = "=MOD(A1,1)"
    Range("A3").FormulaArray = _
        "=IF(MOD(A1,1) >= " & CDbl(TimeValue(timeCompare)) & ",1,0)"
End Sub

I'm using the spanish version of Excel 2007 and the decimal separator here is the comma and not the dot. Is there a way, over VBA, to make this comparison?

 

[shg]

xenou makes a good point. 'Tis true what he suggests, It's getting messier to do this with formulas:

Sub test()
    Dim timBeg      As Date
    Dim timEnd      As Date
 
    timBeg = #8:00:00 PM#
    timEnd = #10:00:00 AM#
 
    With Range("A1")
        .Value = DateSerial(1987, 10, 20) + #9:00:00 PM#
        .NumberFormat = "dd mmm yyyy hh:mm"
 
        .Offset(1).Formula = "=mod(A1,1)"
        .Offset(1).NumberFormat = "hh:mm"
 
        .Offset(2).Formula = "=and(a2 >= --" & Format(timBeg, "\""h:mm\""") & _
                                  ", a2 <= " & Format(timEnd, "\""h:mm\""") & _
                                     "+ (--" & Format(timBeg, "\""h:mm\""") & _
                                      "> --" & Format(timEnd, "\""h:mm\""") & "))"
    End With
End Sub

 

[shg]

Or ...

Sub test()
    Dim timBeg      As Date
    Dim timEnd      As Date
    Dim sBeg        As String
    Dim sEnd        As String
 
    timBeg = #8:00:00 PM#
    timEnd = #10:00:00 AM#
 
    sBeg = Format(timBeg, "\""h:mm\""")
    sEnd = Format(timEnd, "\""h:mm\""")
 
    With Range("A1")
        .Value = DateSerial(1987, 10, 20) + #9:00:00 PM#
        .NumberFormat = "dd mmm yyyy hh:mm"
 
        .Offset(1).Formula = "=mod(A1,1)"
        .Offset(1).NumberFormat = "hh:mm"
 
        .Offset(2).Formula = "=and(a2 >= --" & sBeg & ", a2 <= " & sEnd & _
                             " + (--" & sBeg & ">--" & sEnd & "))"
    End With
End Sub

 

[BeRniTo]

It fails if date's time is #9:00:00 AM#

 

[shg]

Because 9AM is before the start time of 8PM.

Consider the case of noon to noon the next day. For any time in the morning, it could be considered to be within the start and stop times if regarded as being on the second day. For any time in the afternoon, it could be considered to be within the start and stop times if regarded as being on the first day.

Can you back up and explain what you're trying to do. It is surely not related to putting formulas on sheets, is it?

 

[BeRniTo]

Here's my array formula to calculate something based on some restrictions:

=SUM(IF(('C:\[stats.xls]Stats'!$O$2:$O$451=1)*(NEW RESTRCTION HERE),'C:\[stats.xls]Stats'!$I$2:$I$451,0))

A comment: it's kind of tedious having to re-write the formula to the english version. In the spanish version, aside from function's names, the separator is a semicolon and not a comma.

I need to add a new restriction: take into account only those rows which satisfy that its date time is in a range specified by the user. As you know, the user may want to know how much was sold between 8PM and 10AM.

Hope I explained myself!

EDIT: I was going to attach the data file I'm based on but there's no way to attach files here?

 

[xenou]

Interesting discussion on this problem. I did give myself a brain cramp and came up with an ugly (and untested) vba function that you might be able to call from the worksheet:

[COLOR="Navy"]Function[/COLOR] Time_Intersect(TimeBeginning [COLOR="Navy"]As[/COLOR] Date, TimeEnding [COLOR="Navy"]As[/COLOR] Date, TimeToCheck [COLOR="Navy"]As[/COLOR] Date) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="SeaGreen"]'//Returns 1 if TimeToCheck is in the interval from TimeBeginning to TimeEnding[/COLOR]
[COLOR="SeaGreen"]'//Returns 0 otherwise[/COLOR]

[COLOR="SeaGreen"]'//Notes:[/COLOR]
[COLOR="SeaGreen"]'   Function returns an error if time duration is[/COLOR]
[COLOR="SeaGreen"]'           more than 24 hours[/COLOR]
[COLOR="SeaGreen"]'   A single date boundary may be crossed (i.e.,[/COLOR]
[COLOR="SeaGreen"]'           from one day to the next,[/COLOR]
[COLOR="SeaGreen"]'           but no more than 24 hours)[/COLOR]

[COLOR="Navy"]Dim[/COLOR] d(2) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Date[/COLOR]

    [COLOR="SeaGreen"]'//Abbreviate inputs[/COLOR]
    d(0) = TimeBeginning - Int(TimeBeginning)
    d(1) = TimeEnding - Int(TimeEnding)
    d(2) = TimeToCheck - Int(TimeToCheck)

    [COLOR="SeaGreen"]'//Default return/errors[/COLOR]
    Time_Intersect = CVErr(xlErrNA)
     
     [COLOR="SeaGreen"]'//Times must be within 24 hours of each other[/COLOR]
     [COLOR="Navy"]If[/COLOR] d(1) - d(0) > 24 [COLOR="Navy"]Then[/COLOR]
         [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Function[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

    [COLOR="SeaGreen"]'//Calculate intersection[/COLOR]
    [COLOR="Navy"]If[/COLOR] d(1) >= d(0) [COLOR="Navy"]Then[/COLOR]
       [COLOR="Navy"]If[/COLOR] d(2) >= d(0) [COLOR="Navy"]And[/COLOR] d(2) <= d(1) [COLOR="Navy"]Then[/COLOR]
           Time_Intersect = 1
        [COLOR="Navy"]Else[/COLOR]
            Time_Intersect = 0
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] d(2) >= d(0) [COLOR="Navy"]Or[/COLOR] d(2) <= d(1) [COLOR="Navy"]Then[/COLOR]
            Time_Intersect = 1
        [COLOR="Navy"]Else[/COLOR]
            Time_Intersect = 0
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

 

[xenou]

Translating my above "logic" into a formula I came up with:

A1: Starting time
B1: Ending Time
C1: Time to check if its in the range from A1 to B1 times

Formula:
=IF(MOD(B1,1)>MOD(A1,1),IF(AND(MOD(C1,1)>=MOD(A1,1),MOD(C1,1)<=MOD(B1,1)),1,0),IF(OR(MOD(C1,1)>=MOD(A1,1),MOD(C1,1)<=MOD(B1,1)),1,0))

------------------------------------------------------------
Workbook with Formula and UDF from last post:
<a href="http://northernocean.net/etc/mrexcel/20101007_dates.zip">Sample Workbook (zip)</a>

md5 hash for zip file is:
1bf2991ab3a0dd004fc643ccf1202d50

 

[BeRniTo]

Thanks for spending your time in this.

It works great but fails when start time is later than end time and date's time is equal to either of the two.

 

[xenou]

I do not get the same results as you. For example,

start time 3:00 PM
end time 10:00 AM
check time 6:30 PM
Result is --> 1
Interpretation is: in the 19 hour period from 3 pm to 10 am, the time 6:30 has been included.

If this is not correct you will need to re-explain the "rules" to me as I have misunderstood them. As for the equals, that was by design with the idea that 10:00 AM is part of the period from 10:00 AM to 10:00 AM - it can be fixed by removing the equals, but then you have precisely one point in time that will never be included in your equation (i.e., when all the values are the same time - an unlikely event in any case).

 

[BeRniTo]

xenou, you've wrongly interpreted my last sentence:

It works great but fails when start time is later than end time AND date's time is equal to either of the two.

I ment when the two things happen at the same time.

As for the equals actually yes, I need them to be included. If the user specifies a range of time he surely want to include the start and end times.

I didn't understand though what you said about the point in time that will never be included and what to change in the code to fix it.