Adding a name to specific time slots within an Excel spreadsheet

[DivineCupcakes777]

I work in Advertising Sales and when I send clients their daily logs in excel, some need to have day parts added to the corresponding time slots. An example of this is:

Date Scheduled Time Aired Program Name
7/4/2016 6:48:08 AM Early Morning
7/4/2016 6:50:08 AM Early Morning
7/4/2016 6:53:24 AM Early Morning
7/4/2016 6:55:14 AM Early Morning
7/4/2016 6:58:38 AM Early Morning
7/4/2016 9:01:34 AM Daytime
7/4/2016 9:17:25 AM Daytime
7/4/2016 9:23:20 AM Daytime

Everything is generated from our reporting except the Program Name, which is the corresponding day part based on when the actual program aired. I would need a macro or a way to have the day part automatically filled out based on the time that the program aired. Here are the day part parameters:

Early Morning: 6am-8:59am
Daytime: 9am-3:59pm (Monday - Friday)
Fringe: 4pm-7:59pm (Monday-Friday)
Prime: 8pm-11:59pm
Late Prime: 12am-1:59am
Overnight: 2am-5:59am
Weekend: 9am-7:59pm (Saturday and Sunday)

Would you have any idea how to do this in Excel? Although I use it extensively, I am a novice when it comes to Excel formulas and macros. Any help would be appreciated!

 

[CalcSux78]

I would attempt a select case statement to identify the time and adjust for weekend.

Function TimeSlot$(dSched As Date)
Dim bWeekDay As Boolean
Dim dTime As Date


If Weekday(dSched, vbMonday) < 6 Then bWeekDay = True
dTime = Format(dSched, "hh:mm:ss")


Select Case dTime
    Case 0 To 0.082639
        TimeSlot = "Late Prime"
    Case 0.083333 To 0.2493
        TimeSlot = "Overnight"
    Case 0.25 To 0.3743
        TimeSlot = "Early Morning"
    Case 0.833333 To 0.999
        TimeSlot = "Prime"
    Case 0.375 To 0.666
        If bWeekDay Then
            TimeSlot = "Daytime"
        Else
            TimeSlot = "Weekend"
        End If
    Case 0.6666 To 0.8327
        If bWeekDay Then
            TimeSlot = "Fringe"
        Else
            TimeSlot = "Weekend"
        End If
End Select
End Function

Excel 2010
	A	B	C
1	Date Schedueled	Time Aired	Program Name
2	7/4/16 6:48:08 AM	Early Morning
3	7/4/16 6:50:08 AM	Early Morning
4	7/4/16 6:53:24 AM	Early Morning
5	7/4/16 6:55:14 AM	Early Morning
6	7/4/16 6:58:38 AM	Early Morning
7	7/4/16 9:01:34 AM	Daytime
8	7/4/16 9:17:25 AM	Daytime
9	7/4/16 9:23:20 AM	Daytime
Sheet1

Cell Formulas
Range	Formula
B2	=TimeSlot(A2)

 

[DivineCupcakes777]

Thank you for your reply! I am attempting this now

 

[CalcSux78]

I was looking into the times a bit closer... Post #2 isn't as specific for time cutoffs so there are a few seconds between the time change that wouldn't fall within one of the cases.

This version uses the excel TimeValue function to convert a string version of the time into a number.

Function TimeSlot$(dSched As Date)
Dim bWeekDay As Boolean
Dim dTime As Date


If Weekday(dSched, vbMonday) < 6 Then bWeekDay = True
dTime = Format(dSched, "hh:mm:ss")


Select Case dTime
    Case 0 To TimeValue("1:59:59 AM")
        TimeSlot = "Late Prime"
    Case TimeValue("2:00 AM") To TimeValue("5:59:59 AM")
        TimeSlot = "Overnight"
    Case 0.25 To TimeValue("8:59:59 AM")
        TimeSlot = "Early Morning"
    Case TimeValue("20:00:00") To TimeValue("23:59:59")
        TimeSlot = "Prime"
    Case TimeValue("9:00:00") To TimeValue("15:59:59")
        If bWeekDay Then
            TimeSlot = "Daytime"
        Else
            TimeSlot = "Weekend"
        End If
    Case TimeValue("16:00") To TimeValue("19:59:59")
        If bWeekDay Then
            TimeSlot = "Fringe"
        Else
            TimeSlot = "Weekend"
        End If
End Select
End Function

 

[DivineCupcakes777]

Thank you so much! I have been out of my office last week and just got back today so I will try this now. This is really appreciated!

 

[DivineCupcakes777]

I've attempted to enter this as a TimeValue Function but I must be putting in the wrong area because it keeps showing the formula in the excel spreadsheet. Would you be able to tell me the correct place to enter the formula? Sorry...I am a novice.

 

[CalcSux78]

The code portion is for VBA. To add the code to your worksheet, follow these steps:

• Press [Alt+F11] to open Visual Basic for Applications.
• In the menu bar, select Insert > Module
• Paste code in this new Module

When done, close the VB windows and you should be back on your worksheet.

Since we are adding code to your workbook, you would need to save your workbook as a macro-enabled document (XLSM extension)