Custom Function that uses times for case 1

[wwgmr]

Hi all I am trying to write a custom function that will be pasted in a row along a columns that are starting at 12:00 AM and move across till they reach 11:45 PM in 0:15 min increments. The row is 4 and 12 am starts at N4. The function will look to the row its in starting at n4 and compair that value to the start time for the client over in colum b5. What I wrote and thought should work is this

Function Schedule(START As Date, BRK1 As Date, LUNCH As Date, BRK3 As Date, endDAY As Date, ColTime As Date) As String
Select Case Time
Case Range(BRK1).Value - TimeSerial(0, 1, 0) To Range(BRK1).Value + TimeSerial(0, 1, 0)
Schedule = "B"
' I tired above incase for some reason the times do not match perfectly off a .0000001 or so. I also tried as below
Case LUNCH - TimeSerial(0, 1, 0) To LUNCH + TimeSerial(0, 30, 0)
Schedule = "L"
End Select

End Function

I still end up with #VALUE in cell. I wat the Schedule= value returned to cell the formula is in. I think it maybe formating issue with the values BRK1 ect.. I think may need Format(time??) or something like that. I thought by setting them as Date that would work?

Thanks in advance for any help clearing this up for me.

 

[SkipVought]

Hi,

your problem is with Time in this statement...

 Select Case Time

The value should be the variable that you are testing NOT Time.

What variable do you need to test? is it Start?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

Sorry I should have posted what I put in the cell

=schedule($B4,$C4,$D4,$E4,$F4,N$3)

I also changed time to colTime That seems to work, silly mistake on my part not using the correct name in the case lol Simple mistakes. Just worked too long on it. Thanks!

Function Schedule(START As Date, BRK1 As Date, LUNCH As Date, BRK3 As Date, endDAY As Date, ColTime As Date) As String
Select Case ColTime
Case Range(BRK1).Value - TimeSerial(0, 1, 0) To Range(BRK1).Value + TimeSerial(0, 1, 0)
Schedule = "B"

This worked for me

 

[wwgmr]

Well it seemed to but maybe I missed something as when I dragged it out it didn't work. Here is the code.


Function Schedule(START As Date, BRK1 As Date, LUNCH As Date, BRK3 As Date, endDAY As Date, ColTime As Date) As String
Select Case ColTime
Case Range(BRK1).Value - TimeSerial(0, 1, 0) To Range(BRK1).Value + TimeSerial(0, 1, 0)
Schedule = "B"
Case Range(BRK3).Value - TimeSerial(0, 1, 0) To Range(BRK3).Value + TimeSerial(0, 1, 0)
Schedule = "B"
Case Range(LUNCH).Value - TimeSerial(0, 1, 0) To Range(LUNCH).Value + TimeSerial(0, 31, 0)
Schedule = "L"
Case Else
Schedule = CVErr(xlErrNA)
End Select

End Function

The function looks like this
=schedule($B4,$C4,$D4,$E4,$F4,N$3)
=Schedule(START , BRK1, LUNCH , BRK3 , endDAY, ColTime)

It draws from cells on left that have the following.
A4 Name
B4 Start time
C4 1st break or brk1
d4 Lunch
e4 last break or brk3
f4 Stop time or end Day

The column headings are on row 3 and again starting at n3 is 12:00 am then O3 is 12:15 ectra..

Not sure why but when I first changed it to this it worked for the first break but when I draged the formula to the other cells it didn't take.

Thanks for quick reply

 

[SkipVought]

You are using Range() improperly. These are all Dates NOT Ranges.

Function Schedule(START As Date, BRK1 As Date, LUNCH As Date, BRK3 As Date, endDAY As Date, ColTime As Date) As String
    Select Case ColTime
        Case BRK1 - TimeSerial(0, 1, 0) To BRK1 + TimeSerial(0, 1, 0)
            Schedule = "B"
        Case BRK3 - TimeSerial(0, 1, 0) To BRK3 + TimeSerial(0, 1, 0)
            Schedule = "B"
        Case LUNCH - TimeSerial(0, 1, 0) To LUNCH + TimeSerial(0, 31, 0)
            Schedule = "L"
        Case Else
            Schedule = CVErr(xlErrNA)
    End Select
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

Thank you that seemed to do it though it doesn't auto update or check when I hit F9 I have to either drag the formula over other cells or double click in the cell to cause it to update. Is that because it is custom Function? Should I place the function on the sheet or workbook or leave it in the Module section. Also is there way to make it part of this work book so it doesn't bring up the warning about macros when the book is open?

 

[SkipVought]

add

Application.Volatile

up front

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

THanks alot for help that did the trick though system does notice it. Here is code for others if they like to try it.

Function Schedule(START As Date, BRK1 As Date, LUNCH As Date, BRK3 As Date, endDAY As Date, ColTime As Date) As String
'This is custom function to take a employees schedule and setup a time line for easy viewing to the right side.

Application.Volatile
If START < endDAY Then 'Checks to see if its normal schedule
Select Case ColTime
Case START - TimeSerial(0, 1, 0) To endDAY
Select Case ColTime
Case BRK1 - TimeSerial(0, 1, 0) To BRK1 + TimeSerial(0, 1, 0)
Schedule = &quot;B&quot;
Case BRK3 - TimeSerial(0, 1, 0) To BRK3 + TimeSerial(0, 1, 0)
Schedule = &quot;B&quot;
Case LUNCH - TimeSerial(0, 1, 0) To LUNCH + TimeSerial(0, 46, 0)
Schedule = &quot;L&quot;
Case Else
Schedule = &quot;X&quot;
End Select
Case Else
Schedule = &quot;&quot;
End Select
ElseIf START > endDAY Then 'Checks to see if end day is in am before start time if so follow below

Const SetTime = #12:00:00 AM#
Select Case ColTime
Case SetTime To endDAY 'Checks to see if the column time is between 12am and the end time
Select Case ColTime
Case BRK1 - TimeSerial(0, 1, 0) To BRK1 + TimeSerial(0, 1, 0)
Schedule = &quot;B&quot;
Case BRK3 - TimeSerial(0, 1, 0) To BRK3 + TimeSerial(0, 1, 0)
Schedule = &quot;B&quot;
Case LUNCH - TimeSerial(0, 1, 0) To LUNCH + TimeSerial(0, 46, 0)
Schedule = &quot;L&quot;
Case Else
Schedule = &quot;X&quot;
End Select
Case Is >= START 'If colTime is greater then start it follows steps below.
Select Case ColTime
Case BRK1 - TimeSerial(0, 1, 0) To BRK1 + TimeSerial(0, 1, 0)
Schedule = &quot;B&quot;
Case BRK3 - TimeSerial(0, 1, 0) To BRK3 + TimeSerial(0, 1, 0)
Schedule = &quot;B&quot;
Case LUNCH - TimeSerial(0, 1, 0) To LUNCH + TimeSerial(0, 46, 0)
Schedule = &quot;L&quot;
Case Else
Schedule = &quot;X&quot;
End Select
Case Else 'If all else false then the colTime is outside the Work hours Leaves blank.
Schedule = &quot;&quot;
End Select
End If
End Function

Works nice for our needs. Hope its helpful to others as I wanted to give back to group for help.