Need formula to look for Evening hours and Early morning hours

Sherry Hamilton

Good Morning,

I have an excel spreadsheet that I have imported into SS. I am trying to create a rollup sheet to add to a dashboard. One of the items that I want to pull is is the number of offices that have "Evening hours" and "Early Morning Hours" based on the office hours provided. All office hours are located in one column as shown below:

Su: CLOSED; Mo: 9:00AM-5:00PM; Tu: 9:00AM-5:00PM; We: 9:00AM-5:00PM; Th: 9:00AM-5:00PM; Fr: 9:00AM-5:00PM; Sa: CLOSED

Su: CLOSED; Mo: 9:00AM-5:00PM; Tu: 9:00AM-5:00PM; We: 9:00AM-5:00PM; Th: 9:00AM-5:00PM; Fr: 9:00AM-5:00PM; Sa: CLOSED

Su: CLOSED; Mo: 8:00AM-6:00PM; Tu: 8:00AM-6:00PM; We: 8:00AM-6:00PM; Th: 8:00AM-6:00PM; Fr: 8:00AM-6:00PM; Sa: 8:00AM-6:00PM

I want to use the following logic: any office with hours past 5:00pm would be "Evening" and any office with hours before 8:00am would be "Early Morning".

Can you help?

Thanks!

Genevieve P.

Hi @Sherry Hamilton,

We can likely use a COUNTIF formula for this, but in order to help build the formula I need to know a little more about your sheet set up.

I don't quite understand how your office hours are presented in this one column... is it a drop-down list with only 3 options (your three lines above, showing the entire week as one selection), or is each day separated out as one option each (for example one row could say "Mo: 9:00AM-5:00PM" and another row could say "Sa: 8:00AM-6:00PM")?

If possible, it would be really helpful to see a screen capture of your sheet, but please block out any sensitive data.

Thanks!

Genevieve

Sherry Hamilton

Hi Genevieve,

The data is being pulled out of another database where we house provider information. The Database that it pulls from allows slots for each day of the week with dropdowns to chose from. I am extracting the data from that database via a report into excel and using datashuttle to update the information each month. In SS, the information is all in one column Sun-Sat as show above. I am trying to create a helper column that would look for any hours before 8:00am or after 5pm to identify it as an office providing "evening" hours. Then I will pull that information into my rollup for my dashboard metrics.

I hope that helps.

Genevieve P.

Hi @Sherry Hamilton

If I understand you correctly, these are the total possible options that could be displayed, each as a separate, individual selection in one dropdown (note, I deleted out duplicates, such as "Su: CLOSED" appearing three times):

From this list, none of the options are earlier than 8am, but we can definitely look for if a cell says "6:00PM" to identify it as Evening Hours.

To search for a keyword (such as 6:00PM) we can use the CONTAINS function, like so:

=COUNTIF([Office Hours]:[Office Hours], CONTAINS("6:00PM", @cell))

Keep in mind that this will count each individual row that contains this hour. If you have the same Office listed multiple times, then we may want to use a COUNT(DISTINCT(COLLECT formula instead to make sure we're counting unique office numbers (instead of counting one office multiple times).

Does that make sense? Let me know if I've misunderstood your sheet set up and I'd be happy to help further.

Cheers,

Genevieve

Sherry Hamilton

Hi @Genevieve P,

I'm sorry if I was not clear. The options are limitless due to the number of possibilities.

I guess my question, is can I a formula that uses a < 8:00am or a greater than 5:00pm? Or will I have to list out each possible time greater than 5:00pm or less than 8:00am in 30 min intervals?

Sherry Hamilton

Just to show you some of the variations: I am wanting to identify each row as being Early or Evening hours using a helper column and then I will pull in the distinct locations with early or evening hours.

Su: CLOSED; Mo: 6:30AM-8:00PM; Tu: 6:30AM-8:00PM; We: 7:00AM-8:00PM; Th: 6:30AM-7:00PM; Fr: 8:00AM-8:00PM; Sa: 7:30AM-5:30PM

Su: CLOSED; Mo: 7:00AM-3:00PM; Tu: 7:00AM-3:00PM; We: 7:00AM-3:00PM; Th: 7:00AM-3:00PM; Fr: 7:00AM-1:00PM; Sa: 7:30AM-12:00PM

Su: CLOSED; Mo: BY APPT; Tu: 7:00AM-5:00PM; We: BY APPT; Th: BY APPT; Fr: BY APPT; Sa: CLOSED

Su: CLOSED; Mo: 7:00AM-3:00PM; Tu: 7:00AM-3:00PM; We: 7:00AM-3:00PM; Th: 7:00AM-3:00PM; Fr: 7:00AM-1:00PM; Sa: 7:30AM-12:00PM

Su: CLOSED; Mo: BY APPT; Tu: 7:00AM-5:00PM; We: BY APPT; Th: BY APPT; Fr: BY APPT; Sa: CLOSED

Su: CLOSED; Mo: 7:00AM-7:00PM; Tu: 7:00AM-7:00PM; We: 7:00AM-7:00PM; Th: 7:00AM-7:00PM; Fr: 7:00AM-7:00PM; Sa: 8:00AM-2:00PM

Su: CLOSED; Mo: 9:00AM-5:00PM; Tu: 9:00AM-5:00PM; We: CLOSED; Th: 9:00AM-5:00PM; Fr: 9:00AM-5:00PM; Sa: 7:00AM-12:00PM

Su: CLOSED; Mo: 9:00AM-5:00PM; Tu: 9:00AM-5:00PM; We: CLOSED; Th: 9:00AM-5:00PM; Fr: 9:00AM-5:00PM; Sa: 7:00AM-12:00PM

Su: CLOSED; Mo: CLOSED; Tu: 7:00AM-2:00PM; We: CLOSED; Th: 7:00AM-2:00PM; Fr: CLOSED; Sa: CLOSED

Su: CLOSED; Mo: 7:00AM-7:00PM; Tu: 7:00AM-7:00PM; We: 7:00AM-7:00PM; Th: 7:00AM-7:00PM; Fr: 7:00AM-5:00PM; Sa: 7:00AM-2:00PM

Su: CLOSED; Mo: 7:00AM-7:00PM; Tu: 7:00AM-7:00PM; We: 7:00AM-7:00PM; Th: 7:00AM-7:00PM; Fr: 7:00AM-5:00PM; Sa: 7:00AM-2:00PM

Su: CLOSED; Mo: 7:00AM-12:30PM; Tu: 7:00AM-12:30PM; We: 7:00AM-12:30PM; Th: 7:00AM-12:30PM; Fr: 7:00AM-12:30PM; Sa: CLOSED

Su: CLOSED; Mo: 7:00AM-4:30PM; Tu: 7:00AM-4:30PM; We: 7:00AM-4:30PM; Th: 7:00AM-4:30PM; Fr: 7:00AM-4:30PM; Sa: CLOSED

Su: CLOSED; Mo: 7:30AM-4:00PM; Tu: 7:30AM-4:00PM; We: 7:30AM-4:00PM; Th: 7:30AM-4:00PM; Fr: 7:00AM-5:00PM; Sa: CLOSED

Su: CLOSED; Mo: 7:30AM-4:00PM; Tu: 7:30AM-4:00PM; We: 7:30AM-4:00PM; Th: 7:30AM-4:00PM; Fr: 7:00AM-5:00PM; Sa: CLOSED

Su: CLOSED; Mo: 7:30AM-4:00PM; Tu: 7:30AM-4:00PM; We: 7:30AM-4:00PM; Th: 7:30AM-4:00PM; Fr: 7:00AM-5:00PM; Sa: CLOSED

Su: CLOSED; Mo: 7:30AM-4:00PM; Tu: 7:30AM-4:00PM; We: 7:30AM-4:00PM; Th: 7:30AM-4:00PM; Fr: 7:00AM-5:00PM; Sa: CLOSED

Su: CLOSED; Mo: 8:00AM-6:00PM; Tu: CLOSED; We: 2:00PM-8:00PM; Th: 7:00AM-5:00PM; Fr: CLOSED; Sa: CLOSED

Su: CLOSED; Mo: 8:00AM-6:00PM; Tu: CLOSED; We: 2:00PM-8:00PM; Th: 7:00AM-5:00PM; Fr: CLOSED; Sa: CLOSED

Su: CLOSED; Mo: 10:0AM-7:00PM; Tu: 9:00AM-6:00PM; We: 9:00AM-6:00PM; Th: 9:00AM-6:00PM; Fr: 7:00AM-4:00PM; Sa: 9:00AM-3:00PM

Su: CLOSED; Mo: 7:00AM-5:00PM; Tu: 7:00AM-5:00PM; We: 9:00AM-6:00PM; Th: 7:00AM-5:00PM; Fr: 7:00AM-4:00PM; Sa: 7:00AM-3:00PM

Su: CLOSED; Mo: 10:0AM-7:00PM; Tu: 9:00AM-6:00PM; We: 9:00AM-6:00PM; Th: 9:00AM-6:00PM; Fr: 7:00AM-4:00PM; Sa: 9:00AM-3:00PM

Su: CLOSED; Mo: 7:00AM-5:00PM; Tu: 7:00AM-5:00PM; We: 9:00AM-6:00PM; Th: 7:00AM-5:00PM; Fr: 7:00AM-4:00PM; Sa: 7:00AM-3:00PM

Su: CLOSED; Mo: 10:00AM-7:00PM; Tu: 9:00AM-6:00PM; We: 9:00AM-6:00PM; Th: 9:00AM-6:00PM; Fr: 7:00AM-4:00PM; Sa: 9:00AM-3:00PM

Su: CLOSED; Mo: 7:00AM-5:00PM; Tu: 7:00AM-5:00PM; We: 7:00AM-6:00PM; Th: 7:00AM-5:00PM; Fr: 7:00AM-4:00PM; Sa: 7:00AM-3:00PM

Su: CLOSED; Mo: 10:00AM-7:00PM; Tu: 9:00AM-6:00PM; We: 9:00AM-6:00PM; Th: 9:00AM-6:00PM; Fr: 7:00AM-4:00PM; Sa: 9:00AM-3:00PM

Su: CLOSED; Mo: 7:00AM-5:00PM; Tu: 7:00AM-5:00PM; We: 7:00AM-6:00PM; Th: 7:00AM-5:00PM; Fr: 7:00AM-4:00PM; Sa: 7:00AM-3:00PM

Su: CLOSED; Mo: 7:00AM-4:00PM; Tu: 7:00AM-4:00PM; We: 7:00AM-4:00PM; Th: 7:00AM-4:00PM; Fr: 7:00AM-4:00PM; Sa: CLOSED

Genevieve P.

Oh I see! Thank you for clarifying, @Sherry Hamilton.

There isn't a way to simply say > 8am, since this number is housed within a text string. The formula will need to search for the individual possibilities, as you noted.

In this instance, you could use the same formula I have above, but look to see if the cell contains either 6:00PM or 6:30PM or 7:00PM ...etc (do any of them go past 11:30pm?)

=COUNTIF([Office Hours]:[Office Hours], OR(CONTAINS("6:00PM", @cell), CONTAINS("6:30PM", @cell), CONTAINS("7:00PM", @cell), CONTAINS("7:30PM", @cell), CONTAINS("8:00PM", @cell), CONTAINS("8:30PM", @cell), CONTAINS("9:00PM", @cell), CONTAINS("9:30PM", @cell), CONTAINS("10:00PM", @cell), CONTAINS("10:30PM", @cell), CONTAINS("11:00PM", @cell), CONTAINS("11:30PM", @cell)))

That would be your formula for the Late Night offices. Then for the early ones, you'd just have to search for the potential early hours:

=COUNTIF([Office Hours]:[Office Hours], OR(CONTAINS("5:00AM", @cell), CONTAINS("5:30AM", @cell), CONTAINS("6:00AM", @cell), CONTAINS("6:30AM", @cell), CONTAINS("7:00AM", @cell), CONTAINS("7:30AM", @cell)))

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

https://community.smartsheet.com/discussion/comment/298487#Comment_298487

It looks like we posted at the same time, thank you for this!

If you're looking to have a helper column for each row (instead of an overall COUNT), then we just need to adjust the formula to look into this row instead of evaluating a range.

You'd create two IF statements and put them together.

Try this instead:

=IF(OR(CONTAINS("6:00PM", [Office Hours]@row), CONTAINS("6:30PM", [Office Hours]@row), CONTAINS("7:00PM", [Office Hours]@row), CONTAINS("7:30PM", [Office Hours]@row), CONTAINS("8:00PM", [Office Hours]@row), CONTAINS("8:30PM", [Office Hours]@row), CONTAINS("9:00PM", [Office Hours]@row), CONTAINS("9:30PM", [Office Hours]@row), CONTAINS("10:00PM", [Office Hours]@row), CONTAINS("10:30PM", [Office Hours]@row), CONTAINS("11:00PM", [Office Hours]@row), CONTAINS("11:30PM", [Office Hours]@row)), "Evening Hours", IF(OR(CONTAINS("5:00AM", [Office Hours]@row), CONTAINS("5:30AM", [Office Hours]@row), CONTAINS("6:00AM", [Office Hours]@row), CONTAINS("6:30AM", [Office Hours]@row), CONTAINS("7:00AM", [Office Hours]@row), CONTAINS("7:30AM", [Office Hours]@row)), "Morning Hours", "Regular Hours"))

However, what would you like the formula to do with the rows where both evening and morning hours are present?

Sherry Hamilton

Hi @Genevieve P ,

That worked! Thank you so much for your help!

Sherry Hamilton

Hi @Genevieve P ,

One more thing...I am using the same dataset to decipher offices with weekend hours. I have used this formula in a helper column and will pull the unique locations after on a roll up.

=IF(AND(CONTAINS("Su: Closed", [Practice Hours]@row), CONTAINS("Sa: Closed", [Practice Hours]@row), OR(NOT(HAS("Su: BY APPT", [Practice Hours]@row)), NOT(HAS("Sa: BY APPT", [Practice Hours]@row)))), 0, 1)

My problem is some of the offices are blank so I want to pull them in to the formula as "closed" but not sure how. They appear in the data string as Su: or Sa: (blank- with no time or "closed" in the field).

Any ideas on how I could do this?

Genevieve P.

https://community.smartsheet.com/discussion/comment/298504#Comment_298504

Hi @Sherry Hamilton

The structure for the HAS function is actually the opposite (you'd list the "range" first, or in this case [Practice Hours]@row, then say the criteria). I would suggest keeping it to CONTAINS since you're looking for a part of text within a longer thread.

The other thing to note is that the second half of your formula is looking to see if the cell does not have specific data, however since the first half is looking for if it does have specific data, then this automatically rules out the second half.

Why don't we break down the logic. Let's list out all the different possibilities which would mean that the office is closed on a weekend:

• A row that has both "Su: Closed" and "Sa: Closed"
• A row that has "Su:" (blank) and "Sa: Closed"
• A row that has "Su: Closed" and "Sa:" (blank)
• A row that has "Su:" (blank) and "Sa:" (blank)

All other possibilities ("Su: BY APPT", "Sa: 7:00AM-3:00PM", etc) will mean at least one weekend day has the potential for being open.

This means we have 4 AND statements that all need to be nested in one big OR statement.

For your blank Su & Sa, we can search a little further in the text string for "Su: ; Mo" since this shows there's no data after Sunday.

The blank Sa is a little trickier as it's the last option in your list. In this instance, we can use the FIND function to find the text "Sa:" and then return the values to the right of this text. If there are no characters to the right, this means that there are no times selected, so we can assume that the time after Sa: is blank.

For example:

=IF(MID([Practice Hours]@row, FIND("Sa:", [Practice Hours]@row), 16) = "Sa:", "No Hours", "Hours")

So instead of just searching for "Sa:", which appears in every cell, we'll search for the formula above and see if there are characters past the letters "Sa:"

Now we need to build the nested IF statement with all 4 possibilities.

=IF(OR(AND(CONTAINS("Su: Closed", [Practice Hours]@row), CONTAINS("Sa: Closed", [Practice Hours]@row)), AND(CONTAINS("Su: ; Mo", [Practice Hours]@row), CONTAINS("Sa: Closed", [Practice Hours]@row)), AND(CONTAINS("Su: Closed", [Practice Hours]@row), MID([Practice Hours]@row, FIND("Sa:", [Practice Hours]@row), 16) = "Sa:"), AND(CONTAINS("Su: ; Mo", [Practice Hours]@row), MID([Practice Hours]@row, FIND("Sa:", [Practice Hours]@row), 16) = "Sa:")), 0, 1)

Note: If you have any rows where "Sa:" doesn't exist, you'll receive an error. Let me know if this happens and we can wrap an IFERROR around a statement to remove this.

Does the above explanation make sense?