Formula to Display Data from a List Based on Day of Week AND Time of DAY??

[Radiatedjay2]

Hi All... I have looked all over and I'm usually pretty good at finding answers on my own but I am at a dead end with this one. Here is what I'm trying to do...

I have an Excel workbook with three sheets:
Sheet 1 = BACK SHEET
Sheet 2 = SUPERVISOR TURNOVER
Sheet 3 = EVENT DATES

On Sheet 1, cell D25, I need it to display data from Sheet 3, cell range E1 - E12 based on the day of the week and the time of that day.

For instance, on Saturday after 3:00 PM I need Sheet 1, cell D25 to display the data from Sheet 3, cell E1 ("Saturday Night Shift")
On Sunday after 3:00 AM I need Sheet 1, cell D25 to display the data from Sheet 3, cell E2 ("Sunday Day Shift")
On Sunday after 3:00 PM I need Sheet 1, cell D25 to display the data from Sheet 3, cell E3 ("Sunday Night Shift")
And so on throughout the week.

Any help will be appreciated!
Thanks,
Justin

 

[Micron]

Based on the conditons you posted, possibly

'Saturday after 3:00 PM Sheet 1, D25 = Sheet 3, cell E1 ("Saturday Night Shift")
' Sunday  after 3:00 AM Sheet 1, D25 = Sheet 3, cell E2 ("Sunday Day Shift")
' Sunday  after 3:00 PM Sheet 1, D25 = Sheet 3, cell E3 ("Sunday Night Shift")

Private Sub workbook_Open()
Dim intDay As Integer, dblHour As Double

intDay = DatePart("w", Now)
dblHour = CDbl(Hour(Now))
If intDay = 7 And dblHour > 0.625 Then Sheets("Sheet1").Range("D25") = Sheets("Sheet3").Range("E11")
If intDay = 1 And dblHour > 0.125 Then Sheets("Sheet1").Range("D25") = Sheets("Sheet3").Range("E12")
If intDay = 1 And dblHour > 0.625 Then Sheets("Sheet1").Range("D25") = Sheets("Sheet3").Range("E13")

End Sub

Note that if any test is True and a subsequent test is also True, the latter will prevail. I used different E ranges to suit my test sheet. That code also assumes Sunday is the 1st weekday. You could also condense a bit by declaring a range for sheet1.

 

[Micron]

something changed in further testing. The double values for the hour are no longer what I expect. Will have to play around some more.

 

[Micron]

change dblHour = CDbl(Hour(Now))
to
dblHour = CDbl(Time)
Somehow that change didn't make it into the code. To reiterate, in your post both of these conditions will be true on Sunday after 3 PM:
' Sunday after 3:00 AM Sheet 1, D25 = Sheet 3, cell E2 ("Sunday Day Shift")
' Sunday after 3:00 PM Sheet 1, D25 = Sheet 3, cell E3 ("Sunday Night Shift")
You might want to introduce a > < comparison, or it might be possible to use Select Case with 2 tests in the expression but I think the highest value would have to be the first Case test for any given day - e.g. .625 for Sunday must come before .125 for Sunday.

 

[Micron]

Too bad there's only a 10 minute window for editing a post, otherwise I'd have only one
I make boo boo:

To reiterate, in your post both of these conditions will be true on Sunday after 3 AM:

As for the Select Case block, an example would be

Private Sub workbook_Open()
Dim intDay As Integer, dblHour As Double
Dim rng As Range

Set rng = Sheets("Sheet1").Range("D25")
intDay = DatePart("w", Now)
dblHour = CDbl(Time)

Select Case True
   Case intDay = 7 And dblHour > 0.625
      rng = Sheets("Sheet3").Range("E11")
 
   Case intDay = 1 And dblHour > 0.625
      rng = Sheets("Sheet3").Range("E12")
 
   Case intDay = 1 And dblHour > 0.125
      rng = Sheets("Sheet3").Range("E13")
End Select

Set rng = Nothing

End Sub

I think that will work, but as noted, for a given day, the later time has to come first (see where intDay = 1 ). Note the use of a range object ( rng ).